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

[sagarsahoo_123]

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!

[Ashish_Mathur]

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]

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]

Hi,

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

[sagarsahoo_123]

Hi @Ashish_Mathur ,

I have shared the PBIX file in your Linkdin now. Here please look at the "Details" page of the PBIX file. After your suggestion, the worktime range is coming fine, but i need to show the data for the previous 3 months from the selected month in the Dashboard. It is showing the data, but unfortunately Worktimerange_test measure when selected in the view in Details page is showing for all months.

Need your help to resolve this. If you look at the measure, i have written the code to display last 3 month data and created two measure for this, named as WorkTimeRange_Test and WorkTime_Test. While worktime_test measure when select is showing for last 3 month but other one is showing for all months data which are available in the data. 

Kindly help to resolve this!

[Ashish_Mathur]

Hi,

Share the download link of the file here.  If data is confidential, anonymise the data in the file before sharing the download link.

[sagarsahoo_123]

Hi @Ashish_Mathur ,

I have already shared the PBIX file in your Linkdin page on Monday. Please checkif you are able to access it or not. When you open the file, "Details" page will open in which my concern is there.

Kindly help here.

[Ashish_Mathur]

Please read my previous reply carefully.

[sagarsahoo_123]

Hi @Ashish_Mathur ,

Hopefully below link would help.

https://1drv.ms/u/s!AqNLfMSghxwyhgVaPes9qbktkg34?e=fX2DCW

In this PBIX the month i have selected month Aug2021 in the Dashboard. So that i can see data in the "Details" page for June,July, Aug 2021. But it shows data for entire year. Kindly help to resolve this. Many thanks for your support till now.

[Ashish_Mathur]

Hi,

Download the PBI file from here.  I have done it for one measure - do it for the other yourself.  I removed the synchronised slicer of Calendar Month.

Hope this helps.

[sagarsahoo_123]

Hi @Ashish_Mathur ,

Tried with other measure but somehow it is not coming correctly though i have changed it to the code which you have provided.month selected is June 2021, but showing for all available months.

Please use the below link to download the file.

https://1drv.ms/u/s!AqNLfMSghxwyhgVaPes9qbktkg34?e=fX2DCW

Please help if i am missing some key points here to add.

[Ashish_Mathur]

Hi,

Please solve it yourself.  I will noto be able to devote any more time to this question.

[sagarsahoo_123]

Hi @Ashish_Mathur ,

Really obliged and appreciated your valuable effort and time to put for this query in a best possible way.

Regards,

Sagar

[ToddChitt]

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]

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.