Forum Discussion

sagarsahoo_123's avatar
sagarsahoo_123
Helper IV
4 years ago

Grouping multiple values from a column by creating calculated column is not coming correctly

Hi All,

I am new to  Power Bi. Need your assistance to resolve grouping multiple values in a column.

Probelm Statement:- I have a column named as "Work time" in which different numerical values are there for different users. Based on this values i need to put them into different group. For example.Written below code to create calculated column.

-------------------------------------------
WorkTime Range =
IF(
VW_NIPPON_PRODUCTIVITY[WT]=0, "Zero Hours",
IF(
VW_NIPPON_PRODUCTIVITY[WT]>0 && VW_NIPPON_PRODUCTIVITY[WT] < 4,"Low Effort",
IF(
VW_NIPPON_PRODUCTIVITY[WT]>=4 && VW_NIPPON_PRODUCTIVITY[WT]< 8,"Medium Effort","High Effort"
)
)
)
----------------------------
But while using above calculated column in the Matrix visual, for some users it is not coming correctly. Please see the yellow highlighted area below. User spending 10.30hrs are coming in Medium category and 7.30hrs spent user coming in High Effort category. 
Any help regarding this would be highly appreciated. Please let me know if any good approach to resolve this!
Need Help

14 Replies

  • ToddChitt's avatar
    ToddChitt
    Super User
    4 years ago

    Don't verify this with a Matrix. Verify it with a Table. And don't do any SUMMARY of your numeric column because your logic is at the COLUMN level, not the aggregate level.

    • sagarsahoo_123's avatar
      sagarsahoo_123
      Helper IV
      4 years ago

      Hi ToddChitt ,

      Thanks for your quick response. Really appreciated. Can you please elaborate more  on what changes i need to do with the code , as i am new to Power BI. It would be really helpful for the beginer like me.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    You should be writing this as a measure.  Write these 2 measures

    Average work hours = average(VW_NIPPON_PRODUCTIVITY[WT])

    WorkTime Range = if([Average work hours]=0,"Zeron hours",if([Average work hours]<4,"Low effort",if([Average work hours]<8,"Medium effort","High effort")))

    • sagarsahoo_123's avatar
      sagarsahoo_123
      Helper IV
      4 years ago

      Hello Ashish_Mathur ,

      Thanks for your quick support and i have created two measures as per your suggestion and tried to build. But still for some users value is not coming. though i have tried with Matrix view. Will try to plot in Table veiw and see the progress.

      Please see in the above 8.30 is coming in both Medium and High category.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        4 years ago

        Hi,

        Share the link from where i can download your PBI file.  Show the problem there clearly.