CODEWITHSUNDEEP
powerbidax
Daphne
Daphne

Reputation: 3

DAX measure to return a grouped value for which the sum of another column is the max

For my Power BI dashboard I have a dataset with terrorist attacks and columns with city and the number of casualties for each attack. I want to extract the city name for which the sum of the casualties is the highest, within the slicer range that is selected(I have a slicer for year, a country map and region slicer). For that I want to create a DAX measure, so that the result changes along with the slicer settings.

  1. I started with SUMMARIZE to create the sum of the casualties for each unique city

  2. Then I used LOOKUPVALUE to return the city with the max number of casualties

City with most casualties = 

VAR Casualties_per_city = 
    SUMMARIZE(
        Global_terrorism_attacks,
        Global_terrorism_attacks[City],
        "Casualties",
        SUM(Global_terrorism_attacks[Casualties])
    )

VAR City = 
    LOOKUPVALUE(
        Casualties_per_city, [City],
        Casualties_per_city, [Casualties], 
        Casualties_per_city, [Casualties] = MAX(Casualties_per_city, [Casualties]) 
        )

RETURN City

The problem is that SUMMARIZE creates a table variable, for which a column (as far as I know) is only accessible by two arguments separated by a comma, and the LOOKUPVALUE function only takes one argument as a reference to a table/column.

I've also tried a couple of other things, like CALCULATE with VALUES and a FILTER, or SELECTCOLUMNS, but the problem always remains the same.

Does anyone know of a way to create a measure that returns a unique value (city) for which the sum of another column is the maximum among other unique values?

Upvotes: 0

Views: 794

Answers (2)

MarcoPrk
MarcoPrk

Reputation: 11

You can try this code:

City with most casualties = 
VAR SelectedRange = SELECTEDVALUE('YourSlicer'[SlicerColumn])

RETURN
SELECTCOLUMNS(
    TOPN(
        1,
        SUMMARIZE(
            FILTER(
                Global_terrorism_attacks,
                YEAR(Global_terrorism_attacks[Date]) = SelectedRange
            ),
            Global_terrorism_attacks[City],
            "TotalCasualties", SUM(Global_terrorism_attacks[Casualties])
        ),
        [TotalCasualties],
        DESC
    ),
    "City", [City]
)

Just make sure to replace 'YourSlicer'[SlicerColumn] with the name of your slicer and column name.

Upvotes: 0

Jos Woolley
Jos Woolley

Reputation: 9062

I'd just extend your current set-up thus:

City with most casualties =
VAR Casualties_per_city =
    SUMMARIZE(
        Global_terrorism_attacks,
        Global_terrorism_attacks[City],
        "Casualties", SUM( Global_terrorism_attacks[Casualties] )
    )
VAR Max_Casualties_per_city =
    MAXX(
        Casualties_per_city,
        [Casualties]
    )
RETURN
    MAXX(
        Casualties_per_city,
        IF(
            [Casualties] = Max_Casualties_per_city,
            Global_terrorism_attacks[City]
        )
    )

Upvotes: 0

Related Questions