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.
Hi,
I am trying to create a measure with Calculate and Divide functions as below (where Table= Roll, Column = Cost Category)
Basically, i have used one filter in the numerator Cost Category = "Approved absence". However, there is no filter in the Denominator. But when i try to use slicer on the cost category, it throws same value for all. Ideally it should give me the value after applying filter to the denominator. Any ideas if i am missing anything in the formula?
Solved! Go to Solution.
Hi @sujitjena ,
Do you have a relationship between 'Periods' and 'Roll‘? If so, the filter from slicer will also effect CALCULATE(SUM('Periods'[No of periods]),'Periods'[Dimension]="Actual")).
I suggest create a slicer table: table = VALUES('Roll'[Cost category]), use it as slicer. As mentioned by lbendlin, use SELECTEDVALUE instead of ALLSELECTED in your formula.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @sujitjena ,
Do you have a relationship between 'Periods' and 'Roll‘? If so, the filter from slicer will also effect CALCULATE(SUM('Periods'[No of periods]),'Periods'[Dimension]="Actual")).
I suggest create a slicer table: table = VALUES('Roll'[Cost category]), use it as slicer. As mentioned by lbendlin, use SELECTEDVALUE instead of ALLSELECTED in your formula.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
I put your formula through daxformatter.com, but it still is hard to understand.
EVALUATE
CALCULATE (
CALCULATE (
SUM ( 'Roll'[Dist Hrs] ),
'Roll'[Cost category] = "Approved absence",
' Roll'[Dimension] = "Actual"
)
/ DIVIDE (
CALCULATE (
SUM ( 'Roll'[Avg HC] ),
'Roll'[Dimension] = "Actual",
ALLSELECTED ( 'Roll'[Cost category] )
),
CALCULATE ( SUM ( 'Periods'[No of periods] ), 'Periods'[Dimension] = "Actual" )
)
)
What's the rationale for the nested CALCULATE ? Why " / DIVIDE " instead of simple multiplication? If you want to use a slider value then you need SELECTEDVALUE() somewhere. ALLSELECTED() is not enough.
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |