Show highest values for each category

[SG-01]

Hi, let's say I have the following data:

Category	Team	Value
A	1	5
B	2	10
C	3	15
D	1	20
A	2	5
B	3	0
C	1	15
D	2	10

For each team I want to show the category where the SUM of value is the most. I want to show this in a graph. So team 1 has the most value in Category D, team 2 in Category D, team 3 in in Category C. The other categories must not be shown in the graph. Also the corresponding values needs to be shown in the graph. How can I do that?

[pratyashasamal]

Hi @SG-01 ,
Try this measure :-

MaxTest = MAXX(VALUES('DimensionTableName'[Category]),[NameOfMeasureWhichSumsTheValues])

Hope this was helpful.
Thanks,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[SG-01]

@pratyashasamal Hi, thanks for your reply! This solution only looks at the maxium value of 'value'? Because I need the category where the SUM of value is the highest for each team.

[pratyashasamal]

@SG-01 , You can try this as well :-

MaxTest = MAXX(VALUES('DimensionTableName'[Category]),MAX([Values]))
Thanks,
Pratyasha samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[SG-01]

@pratyashasamal If I use this one, I get for every category the highest value of sum. That is not what I want. I need for each team only 1 category, not all of them with the highest values. How can I do that?

[pratyashasamal]

Try TOPN function once 

[SG-01]

On the measure? That's not possible in Power BI. Or I can use TOPN for category or team but that does not seem to work.

[Anonymous]

Hi @SG-01 ,

According to your statement, I think in Team2, B and D should both be the category with highest value.

Measure:

Category with highest value in each Team =
VAR _Step1 =
    FILTER (
        ADDCOLUMNS (
            'Table',
            "Sum each Category", CALCULATE ( MAX ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Team] ) )
        ),
        [Value] = [Sum each Category]
    )
RETURN
    CONCATENATEX ( _Step1, [Category], "," )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SG-01]

@Anonymous Hi there. This is a good solution, however I need the SUM of "Value" which is the highest for each category. Not the single value of "Value" which is the highest. Do you understand?

[SG-01]

@Anonymous Hi there, do you have a new solution? Because I think your solution was in a good direction.

[pratyashasamal]

Hi @SG-01 ,
You can use TOPN function to get the highest sum of sales value by category.
For example :-

EVALUATE

    TOPN (

        1,

        ADDCOLUMNS (

            VALUES ( 'Product'[Product Category] ),

            "@Sales Amount", [Sales Amount]

        ),

        [@Sales Amount],

        DESC

    )

ORDER BY [@Sales Amount] DESC
Example 2 :-

TopN 2nd example = 
TOPN(
    2,
    VALUES(DimProduct[Category]),
    CALCULATE(SUM(FactInternetSales[SalesAmount]))
)

You can refer to this link :-
https://dax.guide/topn/

https://radacad.com/topn-dax-function-how-it-works-in-power-bi-comparison-against-the-top-group

Thanks,
Pratyasha Samal

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C