How to retrieve the category with max sum of sales using a measure to use with card visual

[Anonymous]

Basically i have some sample data that looks like this:

category	sales
product A	100
product A	100
product B	100
product B	50
product B	200
product C	200

And i need a measure that can bring  "product B" as the result in a card, because the sum of sales would be the maximum value for product B:

Category	Sum of Sales
product B	350
product C	200
product A	200

I've seem some other examples here in the community but none of then had a sum inside of the calculation, all where based on retrieving the maximum value directly of an existing database...

Thanks in advance!

[mahoneypat · Accepted solution]

Here's one way to do it.  Replace "Data" with your actual table name.

Max Category =
VAR vSummary =
    ADDCOLUMNS (
        VALUES ( Data[category] ),
        "cSumSales",
            CALCULATE (
                SUM ( Data[sales] )
            )
    )
RETURN
    MINX (
        TOPN (
            1,
            vSummary,
            [cSumSales], DESC
        ),
        Data[category]
    )

Regards,

Pat

[Ashish_Mathur]

Hi,

Write these measures

Total sales = SUM(Data[sales])

Top selling category = FIRSTNONBLANK(TOPN(1,VALUES(Data[category]),[Total Sales]),1)

Drag the second measure to a card visual.

Hope this helps.

[CNENFRNL · Accepted solution]

Hi, @Anonymous , you might want to try such a solution,

Max Category = 
VAR __sum = SUMMARIZECOLUMNS ( Table1[category], "Total", SUM ( Table1[sales] ) )
RETURN
    MAXX ( TOPN ( 1, __sum, [Total] ), Table1[category] )

[mahoneypat · Accepted solution]

Here's one way to do it.  Replace "Data" with your actual table name.

Max Category =
VAR vSummary =
    ADDCOLUMNS (
        VALUES ( Data[category] ),
        "cSumSales",
            CALCULATE (
                SUM ( Data[sales] )
            )
    )
RETURN
    MINX (
        TOPN (
            1,
            vSummary,
            [cSumSales], DESC
        ),
        Data[category]
    )

Regards,

Pat

[Anonymous]

Thanks for the help, this solution worked! Could you give me an explanation of why MINX were used instead of the equivalent MAXX?? I didn't actually understand what happened here!

Also, how would you use this value to bring another unique value from another table that has a relationship? It happens that in my actual data, product A is a product code like PD-001, and it has a better description on a separate table.

Regards,

Leo