Using the SUMMARIZE function in a MAXX calculation

Question

I am trying to aggregate the following values (NHS, Social Care and Both B) by the reasons for delays column so i can find the reason with the highest value (from the 3 combined values named above).

I have tried using summarize to create a table with just the reasons for delays ,NHS, Social Care and Both B columns. By doing this i hoped i could create a column named totals which adds the NHS, Social Care and Both B Columns together in this summarized table thus giving me the total values for each reason for delay.

Though when i tried to run a maxx function around my totals column it seems to give me the wrong values.

I have tried wrapping my table with the distinct function so it aggregates all the columns in my summarize together, but this did not help either.

Max Delays =
MAXX (
    SUMMARIZE (
        csv,
        csv[Reason For Delay],
        csv[NHS],
        csv[Social Care],
        csv[Both B],
        "totals", CALCULATE ( SUM ( csv[NHS] ) + SUM ( csv[Both B] ) + SUM ( csv[Social Care] ) )
    ),
    [totals]
)

The smaller table (which should represent the summarized table) in the above picture with the total column shows the values i expect to carry my max calculation over, where i expect the max value to be 277.

The max value i am getting instead is 182. This is the max value in the unsummarized table below where i have multiple duplicates of my reasons for delay column and 182 is the highest value.

I have uploaded a sample of the pbix file i am working on if it may be of help;https://www.zeta-uploader.com/en/1184250523

RADO RADO · Accepted Answer · 2019-05-28T12:32:24

First, create a measure for total reasons:

Total Reasons = SUM(csv[NHS]) + SUM(csv[Both B]) + SUM(csv[Social Care])

Second, create a measure for the max reason:

Max Reason = MAXX( VALUES(csv[Reason For Delay]), [Total Reasons])

Result:

How it works:

The first measure is for convenience. You can re-use it in other formulas, making code cleaner;
In the second measure, we create a list of distinct reasons using VALUES. Then MAXX iterates this list, calculates total for each reason, and then finds the largest of them.

Using the SUMMARIZE function in a MAXX calculation

1 Answers