Combining Data of Two columns

[kennyyip]

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

[v-piga-msft · Accepted 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