Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |