How to use filter condition in a measure which in turn can be filtered based on slicer

[sujitjena]

Hi,

I am trying to create a measure with  Calculate and Divide  functions as below (where Table= Roll, Column = Cost Category)

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")))

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?

[v-deddai1-msft · Accepted 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

[lbendlin]

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.