Suggestion needed

[shashar11]

Hello -

I need suggestions for building a PBI visual. Please see the below sample data:

Employee ID	Sold Items	Price
1	Fruits	10
1	Flowers	5
1	Vegetable	15
2	Flowers	5
3	Fruits	10
3	Flowers	5
4	Vegetables	15
4	Fruits	10
5	Fruits	10

The visual should show the items sold at the highest price by any employee.

The output should be like- Employee ID- 1 sold vegetables, Employee Id-2 sold Flowers, Employee Id- 3 sold Fruits,

EmploeeID -4 sold Vegetables and Employee Id 5 sold Fruits.

Can someone please suggest?

Thank you!

[v-frfei-msft · Accepted solution]

Hi @shashar11 ,

Here we go.

Measure = 
VAR m =
    CALCULATE (
        MAX ( 'Table'[Price] ),
        ALLEXCEPT ( 'Table', 'Table'[Employee ID] )
    )
RETURN
    IF ( MAX ( 'Table'[Price] ) = m, 1, BLANK () )

Pbix as attached.

[shashar11]

Thank you so much! I appreciate your help on this.

[VasTg]

@shashar11 

Create a measure like below and use it in the visual. Replace the table name as desired

Measure = VAR EMP_ID = MAX('Table (2)'[Employee ID])
RETURN CALCULATE(MAX('Table (2)'[Sold Items]),FILTER(ALL('Table (2)'),'Table (2)'[Employee ID]=EMP_ID && 'Table (2)'[Price]=MAX('Table (2)'[Price])))

Output:

 

If it helps, mark it as a solution

Kudos are nice too

[shashar11]

@VasTg 

Thanks for your suggestion. However, when I used your measure I got no/blank output.

Measure 21 = VAR EMP_ID = MAX(Emp_Test[Employee ID])

RETURN CALCULATE(MAX(Emp_Test[Sold Items]),FILTER(ALL(Emp_Test),Emp_Test[Employee ID]=EMP_ID && Emp_Test[Price]=MAX(Emp_Test[Price])))

Please note: I just replaced the table name ->Emp_Test

Let me know what I am doing wrong?

Thanks!