Create unique list from 2 columns and loop through 2 columns to sum values based on unique list

[asmirnoffnorth]

Hi all,

Having scoured numerous posts I am still struggling to find a solution for a report I am trying to transition over to PowerBI, from MS Excel.

Problem

Create a table in the report section of PowerBI, which has a unique list of currencies (based on 2 columns) and their corresponding FXexposure, which are defined based on each currency leg from 2 columns. Below I have shown the source data and workings I use in Excel, which i am trying to replicate.

Source data (from database table)

a	b	d	d	e	f	g
Instrument	Currency 1	Currency 2	FX nominal 1	FX nominal 2	FXNom1 - Gross	FXNom2 - Gross
FWD EUR/USD	EUR	USD	-7.965264529	7.90296523	7.97	7.90
FWD USD/JPY	USD	JPY	1.030513307	-1.070305687	1.03	1.07
Instrument 1	USD		1.75862819		1.76	0.00
Instrument 2	USD	TRY	0	3.45E-04	0.00	0.00
Instrument 3	JPY		1.121782037		1.12	0.00
Instrument 4	EUR		6.2505079		6.25	0.00
FWD EUR/CNH	EUR	CNH	0.007591392	3.00E-09	0.01	0.00
Instrument 5	RUB		6.209882675		6.21	0.00

F2 = ABS(FX nominal 1)

G2 = ABS(FX nominal 2)

Report output in excel

a	b	c	d	e
FX	Long	Short	Net	Gross
0	0.00	0.00	0.00	0.00
RUB	6.21	0.00	6.21	6.21
EUR	6.26	-7.97	-1.71	14.22
JPY	1.12	-1.07	0.05	2.19
USD	10.69	0.00	10.69	10.69
CNH	0.00	0.00	0.00	0.00
TRY	0.00	0.00	0.00	0.00

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, ">0"))+(SUMIFS(Data!$E$2:$E$553, Data!$C$2:$C$553, Report!$A2, Data!$E$2:$E$553, ">0")))

C2: =((SUMIFS(Data!$D$2:$D$553, Data!$B$2:$B$553, Report!$A3, Data!$D$2:$D$553, "<0"))+(SUMIFS(Data!$E$2:$E$553, Data!$C$2:$C$553, Report!$A3, Data!$E$2:$E$553, "<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))

Now I’ve tried the following approach:

• create a new calculated table for the unique list of currencies:
• Unique Currencies = distinct (union(values(REPORT[CURRENCY1ID]),values(REPORT[CURRENCY2ID])))
• But then for the life of me can’t figure out how to loop through the FXNom1 and FXNom2 columns to SUM each value per unique currency

Any help would be greatly appreciated.

Many thanks!

[asmirnoffnorth]

Hi all,

So I have been plugging at this. I have come to a very dirty hack, but its definitely still wrong as the system really struggles to detect a clean graph (the visualisation looks VERY small, as if it thinks there is hidden data)

I've written the following query to pull the data and then summarise:

FX =

DISTINCT (

UNION (

SELECTCOLUMNS ( DATA, "Date", [DATE], "Currency", [CURRENCY1],  "FXNom", [FXNOMINAL1] ),

SELECTCOLUMNS ( DATA, "Date", [DATE], "Currency", [CURRENCY2], ,"FXNom", [FXNOMINAL2] )

)

)

Anyone have any better solutions?

Thanks
Alex 

[v-shex-msft]

Hi @asmirnoffnorth,

You can refer to the following link to do 'unpivot columns' on these value fields then you can simply summary the 'value' field based on conditions on the 'attribute' field and raw category.

Unpivot columns (Power Query) - Excel (microsoft.com)

Regards,

Xiaoxin Sheng

[asmirnoffnorth]

hey, thank you for your response. I've now come back to this little problem. Can i be cheeky and ask for some examples? Not quite sure that this method will give me what I want. Suprised there isn't a condition to create a table with unique values from the FX 1 and 2 columns, and then its a simple SUM function across the value fields surely?

[asmirnoffnorth]

Hi all - just checking in to see if anyone has any good ideas for the problem I am having. many thanks