Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

max value for 2 categories

Hi All,

 

i am having hard time finding the max value product.

below is my table of data. for argentina 1336 is the max sold value and peripherals is the max sold item.

i am able to get the max sold value with this measure 

Maxvalue =
MAXX(
    KEEPFILTERS(VALUES(Sales[Product])),
    CALCULATE(SUM('Sales'[AMOUNT_SOLD]))
)
but i am unable to get the max sold item name which is peripherals i have tried many many dax functions but i am not able to get that. kindly help.
my ultimate result should be for each country one record with max sold item and its value.

johnbasha33_0-1625651253279.png

@amitchandak @Anonymous @dax @Pragati11 @superDAX @EDW 

1 ACCEPTED SOLUTION

@Anonymous , sorry my mistake, Try like

Rankx(filter(allselected('Sales'[Country], 'Sales'[Product]),'Sales'[Country] =max('Sales'[Country])), CALCULATE(SUM('Sales'[AMOUNT_SOLD])),,desc)

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

daxer_0-1625655506929.png

daxer_1-1625655570985.pngdaxer_2-1625655627845.png

Max Amount Prod = 
IF( not ISBLANK( [Total Amount] ),
    CALCULATE(
        MAX( T[Amount] ),
        VALUES( Country[Country] ),
        ALLSELECTED(  )
    )
)

Max Prod = 
If( not ISBLANK( [Total Amount] ),
    var MaxProdAmount = [Max Amount Prod]
    return
    CALCULATE(
        MAXX(
            FILTER(
                SUMMARIZE(
                    T,
                    Country[Country],
                    'Product'[Product]
                ),
                [Total Amount] = MaxProdAmount
            ),
            'Product'[Product]
        ),
        VALUES( Country[Country] ),
        ALLSELECTED( )
    )
)
amitchandak
Super User
Super User

@Anonymous , Based on what I got. Create a rank and apply visual filter for rank =1

 

Rankx(allselected('Sales'[Country], 'Sales'[Product]), CALCULATE(SUM('Sales'[AMOUNT_SOLD])),,desc)

Anonymous
Not applicable

@amitchandak , thanks for your time.

i tried that measure but not working as we want.

johnbasha33_0-1625653347777.png

 

@Anonymous , sorry my mistake, Try like

Rankx(filter(allselected('Sales'[Country], 'Sales'[Product]),'Sales'[Country] =max('Sales'[Country])), CALCULATE(SUM('Sales'[AMOUNT_SOLD])),,desc)

Anonymous
Not applicable

It worked @amitchandak  thank you.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors