Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am very new to Power BI ...both DAX or M formula in query editor...need help from veteran here.
I have a set of data like this.
And the thing I would like to do is to sum the notional whenever it appears in Column 1 or 2 so the result is as below (either in absolute figures of % is fine). My end goal is to create a pie chart in % term per below (and will have some tooltip chart from different currencies so I cannot just manually amend the data in excel...)
Any advice?
One approach I tried is to calculate of % total with DAX
% Notional = divide([Notional], calculate([Total Notional],all(Table)))
Then create two table with currency buy / currency sell and the % notional, merge two new table again into
USD 1000
HKD 2000
RMB 2000
EUR 3000
HKD 1000
RMB 2000
USD 5000
and pivot to create a final table
But the problem is I could only pivot in query editor but I could not work out the % notional as my List.Sum never gets the total notional in the column (i.e. Total Notional = List.Sum({[Notional]}) render the notional for each row... List.Sum([Notional]) renders error message.
Sorry I am not familiar to syntax so my error may be stupid. Thanks
Solved! Go to Solution.
Hi @kennyyip,
You could get your expected output by the steps below.
Go to Query Editor ——Transform——select both Currency Buy and Currency Sell with 'Ctrl'——Click Unpivot Columns—— Rename the Value to Currency—— Remove the column Attribute.
Then Close & Applay. In report view, you could create the visuals below.
Hope it can help you!
Best Regards,
Cherry
Hi @kennyyip,
You could get your expected output by the steps below.
Go to Query Editor ——Transform——select both Currency Buy and Currency Sell with 'Ctrl'——Click Unpivot Columns—— Rename the Value to Currency—— Remove the column Attribute.
Then Close & Applay. In report view, you could create the visuals below.
Hope it can help you!
Best Regards,
Cherry
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |