Categorize currencies(and not only) in live connection to analytical service

[chaikovskyii]

Hi guys!

I'm kind of new to Power BI, so need help 😒

I did a live connection to analytical service, so I cannot create a new custom column

My table looks (simplified) like this:

Scenario	Currency	Volume_Assets	Volume_Liabilities	Date_to	Performing
Actual	USD	5	0	202410	No
Actual	EUR	0	10	202409	Yes
Budget	CHF	0	3	202411	Yes
Forecast	EUR	22	0	202411	No
Budget	EUR	8	0	202412	Yes
Forecast	CAD	0	15	202312	No

And I need to create a Power BI report that will look like this:

Portfolio	01.01.2024	Today	Increase	Forecast	Forecast Diff	Budget	Budget Diff
Perf.Loans	8	22	14	20	2	25	-3
Local Currency (USD)	3	10	7	10	0	15	-5
Foreign Currency (everything that != USD)	5	12	7	10	2	10	2
Liabilities	..	..	..	..	..	..	..
Local Currency (USD)	..	..	..	..	..	..	..
Foreign Currency (everything that != USD)	..	..	..	..	..	..	..

A row in my source dataset can be considered a performing loan if Volume_Assets!=0 and Performing = "Yes"

Also it can be considered a liability if Volume_Liabilities !=0

How to split my data into Loans and Liabilities and further into Local Currency and Foreign Currency?

For currencies, i've tried creating measures, but I cannot put them into rows of the table, as far as I understand 😒

Will be grateful for any help, take care!👍

[Anonymous]

Hi @chaikovskyii ,

Thanks for lbendlin's reply!

@chaikovskyii , I roughly understand what you want to achieve, but I'm not quite sure how you are doing the calculations because the data you provide and the expected results don't seem to match. Could you provide us with the corresponding results for the sample data you provided so that we can better understand it?

In addition, I don't quite understand your expected results. Where does these columns come from?

Also, do these fields exist in a certain table? Or do these fields not exist and we need to create them ourselves?

Best Regards,
Dino Tao

[lbendlin]

 i've tried creating measures, but I cannot put them into rows of the table, as far as I understand 

Not directly. But what you can do is create a reference table with all the column headers you want, and then create a new measure that switches the calculation based on the column value.