Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

adriandumitru_0-1652452778973.png

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!

1 ACCEPTED SOLUTION
PaulOlding
Solution Sage
Solution Sage

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

PaulOlding_0-1652621614642.png

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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.

adriandumitru_0-1652683826770.png

 

PaulOlding
Solution Sage
Solution Sage

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

PaulOlding_0-1652621614642.png

 

Anonymous
Not applicable

Thank you, Paul! Is what I wanted!

I liked your idea with <="6"

HenriqueReis
Resolver I
Resolver I

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.
HenriqueReis_0-1652608947824.png
 
Then, create a dash like this one and put the two measures:
HenriqueReis_1-1652609098832.png

 

I really hope I help you

 

Regards from Brazil!

 
Anonymous
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.