Forum Discussion

shashar11's avatar
shashar11
Frequent Visitor
6 years ago
Solved

Suggestion needed

Hello -

 

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

 

Employee IDSold ItemsPrice
1Fruits10
1Flowers5
1Vegetable15
2Flowers5
3Fruits10
3Flowers5
4Vegetables15
4Fruits10
5Fruits10

 

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!

 

Show and Tell
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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.

     

4 Replies

  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

    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's avatar
      shashar11
      Frequent Visitor
      6 years ago

      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!
       
       
  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    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's avatar
      shashar11
      Frequent Visitor
      6 years ago

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