topic Re: Calculate Percent of a specific row from other rows in a column in DAX Commands and Tips

Calculate Percent of a specific row from other rows in a column

Anonymous — Fri, 13 May 2022 14:44:08 GMT

Hello, I would like to create a measure in DAX to calculate Percent of CAB and CAN for the specified rows.

I marked with blue CAB and with green CAN. This needs to be dynamic because there is a hierarchy on Level 1(every row is splited on other 3 types).

Thank you!

Re: Calculate Percent of a specific row from other rows in a column

HenriqueReis — Sun, 15 May 2022 10:06:18 GMT

Hi, Adrian.

Hmm, I think you should create two diferent measures with these formula:

Formula 1 -----> CAN = CALCULATE(COUNT(Tabela[% of CAB/CAN]),Tabela[% of CAB/CAN] = "Amount/06.CAN")

Formula 2 -----> CAB = CALCULATE(COUNT(Tabela[% of CAB/CAN]),Tabela[% of CAB/CAN] = "Amount/01.CAB")

Just create a measure copy and paste for each one.

Then, create a dash like this one and put the two measures:

I really hope I help you

Regards from Brazil!

Re: Calculate Percent of a specific row from other rows in a column

PaulOlding — Sun, 15 May 2022 13:36:36 GMT

Hi Anonymous

Here's a measure that gets you the results in Column E of your screenshot. I didn't quite follow what you meant by 'hierarchy on Level 1' but hopefully this works.

% of CAB/CAN = VAR _Level1 = SELECTEDVALUE('Table'[Level 1]) VAR _CABCAN = IF(_Level1 <= "06", CALCULATE(SUM('Table'[Amount]), 'Table'[Level 1] = "01. CAB - CIFRA DE AFACERI BRUTA"), CALCULATE(SUM('Table'[Amount]), 'Table'[Level 1] = "06. CAN - CIFRA DE AFACERI NETA") ) VAR _Result = DIVIDE(SUM('Table'[Amount]), _CABCAN) RETURN _Result

Notice you're getting a result on the total line. You may want to stop that by returning

IF(NOT ISBLANK(_Level1), _Result) instead of _Result

Re: Calculate Percent of a specific row from other rows in a column

Anonymous — Mon, 16 May 2022 06:08:39 GMT

Thank you, Paul! Is what I wanted!

I liked your idea with <="6"

Re: Calculate Percent of a specific row from other rows in a column

Anonymous — Mon, 16 May 2022 06:10:19 GMT

Hello, Henrique! This is also helpfull. Thank you!

Re: Calculate Percent of a specific row from other rows in a column

Anonymous — Mon, 16 May 2022 06:50:37 GMT

Hi, Again! What I wanted to say about Hierarchy is something like this:

Every row in level 1 has other rows in other Levels. Level 2, for example is a split between Reatil and Online. Then Retail will be splited in level 3 in other types and so on.