cancel
Showing results for
Did you mean:
Frequent Visitor

## Suggestion needed

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.

Thank you!

1 ACCEPTED SOLUTION
Community Support

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.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
4 REPLIES 4
Community Support

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.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Frequent Visitor

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

Memorable Member

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

Frequent Visitor

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!

Announcements