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

[Anonymous]

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!

[Anonymous]

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.

[PaulOlding · Accepted solution]

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

[Anonymous]

Thank you, Paul! Is what I wanted!

I liked your idea with <="6"

[HenriqueReis]

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!

[Anonymous]

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