SUM of MAX values in a table

[jmarcrum]

Hi all - I'm trying to reproduce an LOD expression (from Tableau) in Power BI. 

{ FIXED [Schedule Period], [Schedule Query], [Site] : MAX( [NAH] ) }

NAH is the difference between two other columns.

What is the easiest way to do this in Power BI?

[OwenAuger · Accepted solution]

Hi @jmarcrum 

A measure like this should give the same result for that particular visual:

Max NAH Measure = 
SUMX ( 
    SUMMARIZE (
        YourTable,
        YourTable[Site],
        YourTable[Schedule Period],
        YourTable[Schedule Query]
    ),
    CALCULATE ( MAX ( YourTable[NAH] ) )
)

Here I've used SUMMARIZE to return a table with the required granularity, and then used SUMX over this table to sum the max values.

To exactly replicate the behaviour of the FIXED LOD (which would ignore filters aside from Site, Schedule Period and Schedule Query) you could also write:

Max NAH Measure = 
SUMX ( 
    SUMMARIZE (
        YourTable,
        YourTable[Site],
        YourTable[Schedule Period],
        YourTable[Schedule Query]
    ),
    CALCULATE (
        MAX ( YourTable[NAH] ),
        ALLEXCEPT (
            YourTable,
            YourTable[Site],
            YourTable[Schedule Period],
            YourTable[Schedule Query]
        )
    )
)

Hopefully that's of some use.

Regards,

Owen