Filtered calculation

[chydewf1]

Hi

I have the following measure which works well, but I would like to make it a little more dynamic.

FTL = CALCULATE(sum(TABLE[Value]),Query1[ChannelName]="X",TABLE[SubtestNumber]=1)-CALCULATE(sum(TABLE[Value]),Query1[ChannelName]="X",TABLE[SubtestNumber]=27)

I would like to set the last filter to find the max subtest number.

I can create a measure that finds the max subtest but cannot unclude that measure in the above calc

FTL = CALCULATE(sum(TABLE[Value]),Query1[ChannelName]="X",TABLE[SubtestNumber]=1)-CALCULATE(sum(TABLE[Value]),Query1[ChannelName]="X",TABLE[SubtestNumber]=[Max Subtest])

Thanks

Chris

[Zubair_Muhammad · Accepted solution]

Hi @chydewf1

Try this one

FTL =
VAR MyMax = [Max Subtest]
RETURN
    CALCULATE (
        SUM ( TABLE[Value] ),
        Query1[ChannelName] = "X",
        TABLE[SubtestNumber] = 1
    )
        - CALCULATE (
            SUM ( TABLE[Value] ),
            Query1[ChannelName] = "X",
            TABLE[SubtestNumber] = MyMax
        )

[chydewf1]

Great stuff, thanks