Matrix - Calculate the differences of one category to all other categories in the same field.

[Kylpat]

Hello, 

I work at finance and recently started learning powerbi. I am used to work with tableau which i find easy but with powerbi i am a bit lost. 

I only focus on building the tables. The data i have is structured the following way: 

1. List of categorical fields like Country, Region, Plans and Buisness

2. List of financial measures, basically around 70 accounts. 

The Matrix i was able to built was 'Plan' as columns and then selected all the measures 'Accounts'  and have them as rows. Simple vizual table would be 

	FY19	PY	Forecast	ACT
Sales	100	120	110	130
Margin	20	24	23	22
Profit	2	3	4	3.5

The idea would be to have 3 more columns with ACT vs FY19 // ACT vs PY // ACT vs Forecast. (probably then add some color to those columns)

Is there an easy way to do this in PowerBi? What would be the best approach? change the database layout and have all accounts as one field and have one field for each plan? the issue of this is the amount of rows generated but let me know what would be your solution. 

If you know of a tutorial and/or video focus on matrix and tables please share!

Thanks!

kind regards,

Kylian

[v-kkf-msft · Accepted solution]

Hi @Kylpat ,

I am not sure if I understood your question correctly. If you need the measure in rows to get the required visual, you need enable the option "Switch values to rows" in matrix visual.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-kkf-msft · Accepted solution]

Hi @Kylpat ,

I am not sure if I understood your question correctly. If you need the measure in rows to get the required visual, you need enable the option "Switch values to rows" in matrix visual.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kylpat]

Hello, 

The swith to row i have it. The problem i have is to calculate the difference  between columns

ACT - Forecast

ACT - PY

ACT - FY 

I am able to do it buy have to:

1. Create a seperate table 

2. Calculate the difference for each measure (one for Sales, one for Margin and one for Profit) 

Is there a quick was to apply the difference between columns all of the accounts rather than one by one? in the real test i have around 70 accounts. 

After looking through internet seems the best way i having no measures a unique column with all values.

kind regards,

Kylian

[v-kkf-msft · Accepted solution]

Hi @Kylpat ,

Probably not the best way, perhaps you could try creating a new table containing the names of all measures.

Then set the field to the row of the matrix and create the following measures:

Measure = 
SWITCH (
    MAX ( MeasureName[Value] ),
    "Margin", [Margin],
    "Profit", [Profit],
    "Sales", [Sales]
)

Diff = 
IF (
    MAX ( 'Table'[Plan] ) = "ACT",
    [Measure],
    [Measure]
        - CALCULATE ( [Measure], 'Table'[Plan] = "ACT" )
)

Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Kylpat , Need some sample source data and final outcome.

But seem like something I discussed here

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0