topic How to do Vertical and Horizontal Analysis in percentages with 3 levels in DAX Commands and Tips

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

carlosroston — Sun, 17 Nov 2024 01:04:46 GMT

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=102705455705218557937&rtpof=true&sd=true

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

tharunkumarRTK — Sun, 17 Nov 2024 05:55:14 GMT

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!

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

carlosroston — Sun, 17 Nov 2024 14:31:30 GMT

Good morning!

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