Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
Thank you so much! I appreciate your help on this.
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
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |