DAX Measure: IF sum of all users = Max THEN return value of individual user

Question

He Everyone,

I newer to DAX measures and I am trying to get my measure to have the following logic:

When sum of all users = Max, THEN return value of individual user

The Data Model has the following columns: CustomerID \ Usage \ Interval (DATETIME). What is tripping me up is that DATETIME is in 15-minute increments. I have approximately 700 unique CustomerIDs and I need to be able to return the usage of each CustomerID during the MAXSUM of all the CustomerIDs.

I am not sure if this would be an IF-THEN statement or if I need to use a time function. I am writing this DAX measure in Power Pivot to send to a Pivot Table within Excel.

Thanks in advance,

Answer 1

This may not be the simplest solution, but it works for me (I've named the table Load).

CustomerUsage = 
    VAR Summarized = CALCULATETABLE(
                         SUMMARIZE(Load, Load[Interval], "TotalUsage", SUM(Load[Usage])),
                         ALLSELECTED(Load))
    VAR MaxUsage = MAXX(Summarized, [TotalUsage])
    VAR MaxInterval = MAXX(FILTER(Summarized, [TotalUsage] = MaxUsage), [Interval])
    RETURN CALCULATE(SUM(Load[Usage]), Load[Interval] = MaxInterval)

First, you generate a table that finds the total usage per interval by summing over all selected users.

The MaxUsage is simply the largest TotalUsage over all of the intervals.

The MaxInterval is the interval for which the TotalUsage = MaxUsage.

Then we can find the Usage for a customer for the interval with maximum usage.