Max Value of a Category

[BH22One]

Good Afternoon,

I am struggling with a solution in Power BI that would give me the Maximum Sales of a Product Category for a particular day.

I used Max Sales = MAX('Table' [Sales]) to find the maximum value in the entire table.

I tried using Max Category Sales = CALCULATE(MAX('Table'[Sales]),ALLEXCEPT('Table','Table'[PRODUCT_ID]) however it returns the same value as the calculation above. I need it to ignore Field 2 (or Product ID) and give the max of the entire category over a period of time.

Based on the picture below, I need the "Max Category Sales" Card to equate to the maximum value for the category shown in the table below the card of 5,186.

Thanks in advance on any help with this.

-BSH

[Syndicate_Admin]

Good afternoon community. I have the following problem: I want the total of my Category to see the same value as this in all subcategories and not to add it, as I can do.

I would appreciate your help!

[Ashish_Mathur]

Hi,

This pattern should work

Measure = if(hasonevalue(Data[Categpria]),[activas_campanial],minx(all(Data[Categpria]),[activas_campanial]))

Hope this helps.

[Ashish_Mathur]

Hi,

Assuming the following are true:

1. There is a Calendar Table with a relationship to the Date column of Table; and
2. To your visual, drag Year and Date from the Calendar Table; and
3. In the slicer/filter, select Headphones
4. "Total sales value" is a measure which is calculated as =SUM('Table'[Sales])

write this measure

Max sales for chosen category on any day = MAXX(SUMMARIZE(Calendar,Calendar[Date],"ABCD",[Total sales value]),[ABCD])

If this does not help, then share the link from where i can download your PBI file.

[Greg_Deckler]

I would think:

= CALCULATE(MAX('Table'[Sales]),ALLEXCEPT('Table','Table'[CATEGORY])

[BH22One]

Good Afternoon @Greg_Deckler ,

I tried your suggestion and unfortunately it gives me the same output as before 😞

[v-lid-msft · Accepted solution]

Hi @BH22One ,

We can use the following measure to meet your requirement.

max category =

CALCULATE (

    MAXX (

        DISTINCT ( 'Table'[POSTING_DT] ),

        CALCULATE ( SUM ( 'Table'[Sales] ) )

    ),

    ALLSELECTED ( 'Table' )

)

Then we can get the result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

Best regards,

[BH22One]

@v-lid-msft This solution worked perfectly.

Thanks a ton!

[Greg_Deckler]

OK, can you share sample data then so I can test this out and get a working solution. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490