Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Can some one tell me what I am missing , below is my DAX , I don't want 100% at the ID level , should be 5.2%, 4.1 % and son when drill down to see ID %
Hi,
I have created a sum of values over three hierarchies in a matrix via isinscope. Great tip, thanks.
The measure works, however, when I utilitize it as a tooltip on the matrix, isinscope seems to result in a never in scope. Where it should give me the percent of total per hierarcy, it now always gives 100%.
This is my measure now:
Hi @Anonymous ,
Try something like this
Sales % =
SWITCH (
TRUE (),
ISINSCOPE ( Brand[ID] ), FORMAT (
DIVIDE (
CALCULATE (
[Total Sales],
ALLEXCEPT (
Brand,
Brand[ID]
)
),
CALCULATE (
[Total Sales],
ALLEXCEPT (
Brand,
Brand[Brand Name]
)
)
),
"Percent"
),
[Total Sales]
)
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
@harshnathani this doesn't work , first of all in my matirx first column is sales dollar value and second is percentage , first column is already working well, second column has the issue , when drill down the hierarchy Id level is showing 100% , puting on your calc creating one column and showing percent under dollar value liek below
what I want is some thing like this
Try this. The percentage at brand level is high because I don't have your full data.
TotalSales = SUM ( Khana[Sales] )
Sales Perc =
VAR TotalID =
CALCULATE ( [TotalSales], ALLSELECTED ( Khana[ID] ) )
VAR GrandTotal =
CALCULATE ( [TotalSales], ALLSELECTED ( Khana ) )
VAR Result =
IF (
ISINSCOPE ( Khana[ID] ),
DIVIDE ( [TotalSales], TotalID ),
IF ( ISINSCOPE ( Khana[Brand] ), DIVIDE ( [TotalSales], GrandTotal ), 1 )
)
RETURN
Result
Hi @Anonymous ,
unclear as to what is needed.
I tried something on dummy data.
Check if this works for you else share some more clarity on your requirement.
Sales % =
VAR a =
CALCULATE (
[Total Sales],
ALLEXCEPT (
Geography,
Geography[City]
)
)
VAR b =
CALCULATE (
[Total Sales],
ALLEXCEPT (
Geography,
Geography[State]
)
)
RETURN
SWITCH (
TRUE (),
ISINSCOPE ( Geography[City] ), DIVIDE (
a,
b
),
DIVIDE (
b,
CALCULATE (
[Total Sales],
ALL ( Sales )
)
)
)
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
The total of ID should Mach up to 14.43% so I am getting osme calculation but the numbers are wrong
@Anonymous - Check out MM3TR&R - https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-Roll/m-p/411443#M150
@Anonymous , refer if this can help
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |