Show highest values for each category

Question

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?

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
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
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;

pratyashasamal · Answer

Hi&nbsp;Anonymous&nbsp;,Try this measure :-MaxTest =&nbsp;MAXX(VALUES('DimensionTableName'[Category]),[NameOfMeasureWhichSumsTheValues])Hope this was helpful.Thanks,Pratyasha SamalHas this post solved your problem? Please&nbsp;Accept as Solution&nbsp;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&nbsp;Kudos&nbsp;C

anonymous · Answer

pratyashasamal&nbsp;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 · Answer

Hi&nbsp;Anonymous&nbsp;,You can use TOPN function to get the highest sum of sales value by category.For example :-EVALUATE&nbsp;&nbsp;&nbsp;&nbsp;TOPN (&nbsp; &nbsp; &nbsp; &nbsp; 1,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ADDCOLUMNS (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES ( 'Product'[Product Category] ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"@Sales Amount", [Sales Amount]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[@Sales Amount],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DESC&nbsp;&nbsp;&nbsp;&nbsp;)ORDER BY [@Sales Amount] DESCExample 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-groupThanks,Pratyasha SamalHas this post solved your problem? Please&nbsp;Accept as Solution&nbsp;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&nbsp;Kudos&nbsp;C

pratyashasamal · Answer

Anonymous&nbsp;, You can try this as well :-MaxTest =&nbsp;MAXX(VALUES('DimensionTableName'[Category]),MAX([Values]))Thanks,Pratyasha samalHas this post solved your problem? Please&nbsp;Accept as Solution&nbsp;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&nbsp;Kudos&nbsp;C

Forum Discussion

Show highest values for each category

10 Replies

Related content

highest value by category

Filter only highest value by category

2nd Highest Value

Find Highest Value of a Column

Highest value in a day