How to do Vertical and Horizontal Analysis in percentages with 3 levels

[carlosroston]

Hello friends!

I have a pbix file with an example of a database. In it I have the categories in rows (3 levels) and the years and months in columns. As if it were an income statement.

Please help me with a vertical and horizontal analysis of the data in percentages.

I'm attaching the files and an image.

Thank you very much for your help.

file.rar (.pbix and .xlsx)

https://drive.google.com/file/d/1C7wTH7rzKXbOg5BrsGdd0J6kxyXuZwr-/view?usp=sharing

file .pbix

https://drive.google.com/file/d/1YNwDlwJtpuk3WRuFoQd4G2l_fIvEs2Ov/view?usp=sharing

file .xlsx

https://docs.google.com/spreadsheets/d/1gZbGvlFG06FR-frxDOkqdHefDlWnMKgb/edit?usp=drive_link&ouid=10...

 

[tharunkumarRTK · Accepted solution]

@carlosroston 

You sorted dDRE[Nivel 1] column with dDRE[Ordem], so if you want to remove filters on [Nivel 1] you need to remove filters on both [Nivel 1] and [Ordem] column. 

If I understand your  expected results correctly then this is how you need to modify your measure

% Valor Orig Parc (n1n2c) V2 (PBI Community) = 
Var __Numerator = [Valor Orig Parc]
Var __Denominator = 
                    SWITCH(
                        TRUE(),
                          ISINSCOPE(dDRE[Categoria 1]),   CALCULATE([Valor Orig Parc],ALLSELECTED(dDRE[Categoria 1], dDRE[Nível 2], dDRE[Nível 1], dDRE[Ordem] ) ),
                          ISINSCOPE(dDRE[Nível 2]), CALCULATE([Valor Orig Parc],ALLSELECTED( dDRE[Nível 2], dDRE[Nível 1], dDRE[Ordem] ) ),
                          ISINSCOPE(dDRE[Nível 1]), CALCULATE([Valor Orig Parc],ALLSELECTED( dDRE[Nível 1], dDRE[Ordem] ) ),
                          CALCULATE([Valor Orig Parc],ALLSELECTED(dDRE[Categoria 1], dDRE[Nível 2], dDRE[Nível 1], dDRE[Ordem] ) )

                        
                    ) 
RETURN DIVIDE(__Numerator, __Denominator) 

This would be the result. My named the measure as [AV2] in the below matrix.

Please be informed I used  [Valor Orig Parc] measure in the denominator if you want to use [Valor Orig Parc Geral] then you can replace it in the demoinator variable. I am attaching the PBIX for your reference.

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[carlosroston]

Good morning!

Could we do a horizontal percentage analysis of a given month in relation to the previous month?