How to retrieve data of one column based on another Numeric column in the same table?

[rahulmmenon]

Hi Team,

I have 4 problems (sample dataset for the senario is also given below.

1. I want to get the name of Category based on the total sale made in a card visual.

2. I want to get the name of Category based on the Highest count of sales (number of transaction) in a card visual.

3. I want to get the name of Sub category  based on the highest sale.

4. I want to get the name of Sub category based on the Highest count of sales (number of transaction) 

[v-stephen-msft · Accepted solution]

Hi @rahulmmenon ,

1. Get the name of Category based on the total sale maximum in a card visual.

sum group by category =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER ( ALLSELECTED ( 'Table' ), [Category] = MAX ( 'Table'[Category] ) )
)

Category based on the total sale maximum =
CALCULATE (
    MAX ( 'Table'[Category] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [sum group by category]
            = MAXX ( ALLSELECTED ( 'Table' ), [sum group by category] )
    )
)

2.Get the name of Category based on the Highest count of sales. The measure returns an aggregate value, and if there are categories with the same highest count, since I used MAX, it will return Mobile in alphabetical order of the categories. 

Count group by category =
CALCULATE (
    COUNT ( 'Table'[Sales] ),
    FILTER ( ALLSELECTED ( 'Table' ), [Category] = MAX ( 'Table'[Category] ) )
)

Category based on the Highest count of sales =
CALCULATE (
    MAX ( 'Table'[Category] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [Count group by category]
            = MAXX ( ALLSELECTED ( 'Table' ), [Count group by category] )
    )
)

You can create the following measure to get the correct categories. Card visuals will only return an aggregated value, which is recommended to be viewed in a table visual.

Hiest Count Category =
IF (
    MAXX ( ALLSELECTED ( 'Table' ), [Count group by category] ) = [Count group by category],
    MAX ( 'Table'[Category] )
)

3.Get the name of Sub category  based on the highest sale.

Sub category  based on the highest sale = 
CALCULATE (
    MAX ( 'Table'[Sub category] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [Sales] = MAXX ( ALLSELECTED ( 'Table' ), [Sales] )
    )
)

For you last question, what's the expected result? I think the count of sales based on each sub category is 1.

Best Regards,

Stephen Tao

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