Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello!
I have a table that shows different products and the material value that is bought in different currencies. The value is displayed in one column with the currency in another column. Each product can be bought in max three different currencies.
I have a table with data like this:
Product ID | Name | Material value 1 | Currency 1 | Material value 2 | Currency 2 | Material value 3 | Currency 3 |
123 | House | 1235 | USD | 345 | EUR | NULL | NULL |
321 | Door | 340 | EUR | 2000 | YEN | 50 | USD |
231 | Window | 230 | USD | 120 | SEK | 300 | EUR |
I want in a visual, to see the total value in each currency where you can see the currency with the total value beside.
Eg.
USD 1515
EUR 985
etc.
The data is imported with direct query
Thank you!
Solved! Go to Solution.
Hi, @KristinG
Thanks for the ideas provided by @DataZoe , I tried to make some changes to the formula
Table2 =
SUMMARIZE (
UNION (
SELECTCOLUMNS (
'Table',
"Product", 'Table'[Product ID],
"Name", 'Table'[Name],
"Currency", 'Table'[Currency 1],
"Value", 'Table'[Material value 1]
),
SELECTCOLUMNS (
'Table',
"Product", 'Table'[Product ID],
"Name", 'Table'[Name],
"Currency", 'Table'[Currency 2],
"Value", 'Table'[Material value 2]
),
SELECTCOLUMNS (
'Table',
"Product", 'Table'[Product ID],
"Name", 'Table'[Name],
"Currency", 'Table'[Currency 3],
"Value", 'Table'[Material value 3]
)
),
[Currency],
[Value]
)
Result:
Table2:
Visual:
Please refer to the attachment below for details
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @KristinG
Thanks for the ideas provided by @DataZoe , I tried to make some changes to the formula
Table2 =
SUMMARIZE (
UNION (
SELECTCOLUMNS (
'Table',
"Product", 'Table'[Product ID],
"Name", 'Table'[Name],
"Currency", 'Table'[Currency 1],
"Value", 'Table'[Material value 1]
),
SELECTCOLUMNS (
'Table',
"Product", 'Table'[Product ID],
"Name", 'Table'[Name],
"Currency", 'Table'[Currency 2],
"Value", 'Table'[Material value 2]
),
SELECTCOLUMNS (
'Table',
"Product", 'Table'[Product ID],
"Name", 'Table'[Name],
"Currency", 'Table'[Currency 3],
"Value", 'Table'[Material value 3]
)
),
[Currency],
[Value]
)
Result:
Table2:
Visual:
Please refer to the attachment below for details
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@KristinG I think this will work for direct query, but you can create a DAX table to make a "Currency" table, then use this measure to utilize it.
DAX Currency Table:
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
@KristinG
Please check the attached file below my signature.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |