Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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
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.
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
Thank you, Paul! Is what I wanted!
I liked your idea with <="6"
Hi, Adrian.
Hmm, I think you should create two diferent measures with these formula:
I really hope I help you
Regards from Brazil!
Hello, Henrique! This is also helpfull. Thank you!
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |