#excel #powerbi #dax #powerbi-desktop
Вопрос:
Просмотрев множество сообщений, я все еще пытаюсь найти решение для отчета, который я пытаюсь перевести на PowerBI из MS Excel.
Проблема
Создайте таблицу в разделе отчета PowerBI, в которой есть уникальный список валют (на основе 2 столбцов) и их соответствующее валютное выражение, которые определяются на основе каждой валютной ветви из 2 столбцов. Ниже я показал исходные данные и методы работы, которые я использую в Excel, которые я пытаюсь воспроизвести.
Исходные данные (из таблицы базы данных)
| a | b | d | d | e | f | g |
|---|---|---|---|---|---|---|
| Инструмент | Валюта 1 | Валюта 2 | Номинальная валюта 1 | Номинальная валюта 2 | FXNom1 — Валовой | FXNom2 — Брутто |
| FWD EUR/USD | Евро | Долл. США | -7.965264529 | 7.90296523 | 7.97 | 7.90 |
| FWD USD/JPY | Долл. США | JPY | 1.030513307 | -1.070305687 | 1.03 | 1.07 |
| Инструмент 1 | Долл. США | 1.75862819 | 1.76 | 0.00 | ||
| Инструмент 2 | Долл. США | пробовать | 0 | 3.45 E-04 | 0.00 | 0.00 |
| Инструмент 3 | JPY | 1.121782037 | 1.12 | 0.00 | ||
| Инструмент 4 | Евро | 6.2505079 | 6.25 | 0.00 | ||
| FWD EUR/CNH | Евро | CNH | 0.007591392 | 3.00 E-09 | 0.01 | 0.00 |
| Инструмент 5 | RUB | 6.209882675 | 6.21 | 0.00 |
F2 = ABS(номинальный FX 1) G2 = ABS(номинальный FX 2)
Вывод отчета в excel
| a | b | c | d | e |
|---|---|---|---|---|
| FX | Длинный | Короткий | Сеть | **Брутто ** |
| 0 | 0.00 | 0.00 | 0.00 | 0.00 |
| RUB | 6.21 | 0.00 | 6.21 | 6.21 |
| Евро | 6.26 | -7.97 | -1.71 | 14.22 |
| JPY | 1.12 | -1.07 | 0.05 | 2.19 |
| Долл. США | 10.69 | 0.00 | 10.69 | 10.69 |
| CNH | 0.00 | 0.00 | 0.00 | 0.00 |
| пробовать | 0.00 | 0.00 | 0.00 | 0.00 |
Мои формулы Excel приведены ниже, чтобы воссоздать то, что я ищу.
A2: =IFERROR(LOOKUP(2, 1/(COUNTIF(Report!$A$1:A1,Data!$B$2:$B$553)=0), Data!$B$2:$B$553), LOOKUP(2, 1/(COUNTIF(Report!$A$1:A1, Data!$C$2:$C$553)=0), Data!$C$2:$C$553)) B2: =((SUMIFS(Data!$D$2:$D$553, Data!$B$2:$B$553, Report!$A2, Data!$D$2:$D$553, "gt;0")) (SUMIFS(Data!$E$2:$E$553, Data!$C$2:$C$553, Report!$A2, Data!$E$2:$E$553, "gt;0"))) C2: =((SUMIFS(Data!$D$2:$D$553, Data!$B$2:$B$553, Report!$A3, Data!$D$2:$D$553, "lt;0")) (SUMIFS(Data!$E$2:$E$553, Data!$C$2:$C$553, Report!$A3, Data!$E$2:$E$553, "lt;0"))) D2: =(SUMIF(Data!$B$1:$B$553,Report!$A3,Data!$D$1:$D$553) SUMIF(Data!$C$1:$C$553,Report!$A3,Data!$E$1:$E$553)) E2: =(SUMIF(Data!$B$1:$B$554,Report!$A3,Data!$F$1:$F$554) SUMIF(Data!$C$1:$C$554,Report!$A3,Data!$G$1:$G$554)) Теперь я считаю, что мне удалось найти взлом с помощью функции UNIQUE/SELECTCOLUMNS, но когда вы пытаетесь отобразить выходные данные, они очень малы (как будто есть другие данные, которые он пытается найти за кулисами). Примечание.Я склонен фильтровать по дате, чтобы получить нужные мне выходные данные (это сопоставляется с использованием связей между другими таблицами данных).
FX = DISTINCT ( UNION ( SELECTCOLUMNS ( DATA, "Date", [DATE], "Currency", [CURRENCY1], "FXNom", [FXNOMINAL1] ), SELECTCOLUMNS ( DATA, "Date", [DATE], "Currency", [CURRENCY2], ,"FXNom", [FXNOMINAL2] ) ) ) Если у кого-нибудь есть какие-либо идеи, я был бы очень признателен, так как я все еще чувствую, что мой обходной путь-это скорее удачный хак.
Спасибо!
Комментарии:
1. итак, проблема в том, что он ищет «другие данные за кулисами» или что-то еще?
2. Привет, Сэм. У меня нет больше «закулисных данных», скорее всего, моя попытка заставить его работать не идеальна. следовательно, посмотрим, не попытается ли кто-нибудь другой сделать это по-другому.
3. Итак, Негативы в D или E короткие, Положительные в D или E длинные, Чистый = короткий длинный, Брутто = Абс(короткий) Абс(длинный)?
4. Ваш подход кажется мне правильным. Я не думаю, что вы хотели бы ОТЛИЧНОГО. Хотя дублированные значения маловероятны с такой точностью, вы бы не хотели, чтобы две одинаковые позиции были сведены в одну. Интересно, выглядит ли ваш график небольшим, потому что вы ожидаете кумулятивных значений по дате? Вам нужно будет создать еще одну меру для такого агрегирующего значения.
Ответ №1:
Подход, который вы используете, выглядит почти идеальным. С точки зрения размерной модели вам нужен один столбец для значений и один столбец для меток валют. Поэтому выбор этих пар в качестве разных таблиц и добавление с ОБЪЕДИНЕНИЕМ-правильный путь. Как правило, я думаю, что лучше выполнить все преобразования, которые вы можете выполнить в power query, использование DAX таким образом может привести к некоторым ограничениям.
Но если мы едем с ДАКСОМ, я действительно думаю, что ты хочешь избавиться от него. Это может привести к тому, что одинаковые позиции будут свернуты в одну строку, и таким образом вы потеряете данные.
FX = UNION ( SELECTCOLUMNS ( FX_Raw, "Date", "FakeDate", "Currency", [CURRENCY 1], "FXNom", [FX nominal 1] ), SELECTCOLUMNS ( FX_Raw, "Date", "FakeDate", "Currency", [CURRENCY 2], "FXNom", [FX nominal 2] ) ) А затем несколько мер:
Long = CALCULATE(sum(FX[FXNom]), FX[FXNom] gt;= 0) Short = CALCULATE(sum(FX[FXNom]), FX[FXNom] lt; 0) Gross = SUMX( FX, if(FX[FXNom] gt; 0, FX[FXNom], 0-FX[FXNom])) Net = SUM(FX[FXNom]) Кажется, это дает желаемый результат:
Комментарии:
1. Ну ладно, в этом есть смысл. Большое спасибо. Это был мой первый раз, когда я прочитал несколько форумов и нашел лучший способ подойти к этому — похоже, я был почти готов there…no хотя вишенка сверху. Могу ли я спросить, какое предложение для PowerQuery? рад, что мне прислали ссылки, и я могу прочитать об этом.