Assign actual's and percentage measures in a single Column

[Anonymous]

Hello Community,

              I am trying to recreate the standard P&L Structure. I need to display Actuals and Percentages in the same column. I created measures for all the percentage calculations, I created a table with account names and measures in a single column and written a SWITCH statement to assign measures to account structure. But the switch statement is giving BLANK values for the measures.

The Output should be Similar to this

Working Trade % of GS	4.13%
Working Trade	254
Volume	3505
PCB % of NS	9.62%
PCB	850

This is the SWITCH statement I had written 

SWITCH =

SWITCH([Number],

7, FORMAT([Working Trade % of GS], "%"),

8, FORMAT([EBIT % of NS],"%"),

9, FORMAT([PCB % of NS],"%"),

SUM('Transaction'[Amount]))

Is there any solution that I can assign those measures to the rows?

I am attaching a Sample pbix file to look into it.

https://1drv.ms/u/s!AlTyyZ_9D_WjiWSoAiARep460MyR

Thanks

[TomMartens · Accepted solution]

Hey @Anonymous 

I changed the measure for [Working Trade % of GS] to this:

Working Trade % of GS = 
VAR Workingtrade = CALCULATE(SUM('Transaction'[Amount]),'Transaction'[Account Name] = "Working Trade", CROSSFILTER('Account Structure'[Acc. No] , 'Transaction'[Acc. No] , None))
VAR Grosssales = CALCULATE(SUM('Transaction'[Amount]),'Transaction'[Account Name] = "Gross Sales", CROSSFILTER('Account Structure'[Acc. No] , 'Transaction'[Acc. No] , None))
Return
DIVIDE(Workingtrade,Grosssales,0)

I've added CROSSFILTER(...) to each of the expressions for the variables, this prevents that the Account from the 'Account Structure' table, that does not exist in the transaction table filter out all the rows in the transaction table.
I also changed the SWITCH measure a little, the FORMAT part to this:

SWITCH = 
SWITCH([Number],
7, FORMAT([Working Trade % of GS] , "0.00%"),
8, FORMAT([EBIT % of NS] , "0.00%"),
9, FORMAT([PCB % of NS] , "0.00%"),
SUM('Transaction'[Amount]))

Then the table will look likt this:

Be aware that I just changed one of the percentage measures.

Hopefully this provides what you are looking for.

Regards,

Tom

[Anonymous]

Thank You @TomMartens