Need to summarize the count of users having work time <6hrs from the below matrix table

[sagarsahoo_123]

Hello Team,

Please help on the below query. I need support to find out total number of users having less than 6 hours work time from the below Matrix table.

Here in the above picture, i have displayed users month wise Work time Details. and in the last it is having total Average of all months work time and i have highlighted users value wherever it is less than 6 hours. But i need the count of total users having overall Average work time less than  6hours and need to display like below.

Summary	HeadCounts
Total number of users <6hours Work time	100

Please guide me using which formula i can achieve this. Appreciate your valuable support.

Regards,

Sagar

[mangaus1111 · Accepted solution]

Hi @sagarsahoo_123 ,

try this measure

Measure = 
COUNTROWS(
FILTER(
ADDCOLUMNS(
  SUMMARIZE(Consolidated_Data,
            Consolidated_Data[Personal ID],
            'Date'[Year],
            'Date'[Month name]
            ),
         "@Hours",CALCULATE(SUMX(Consolidated_Data,Consolidated_Data[WT]))
         ),
     [@Hours] < 6 && [@Hours] > 0
     )
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mangaus1111 · Accepted solution]

Hi @sagarsahoo_123 ,

try this measure

Measure = 
COUNTROWS(
FILTER(
ADDCOLUMNS(
  SUMMARIZE(Consolidated_Data,
            Consolidated_Data[Personal ID],
            'Date'[Year],
            'Date'[Month name]
            ),
         "@Hours",CALCULATE(SUMX(Consolidated_Data,Consolidated_Data[WT]))
         ),
     [@Hours] < 6 && [@Hours] > 0
     )
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mangaus1111 · Accepted solution]

Hi @sagarsahoo_123 ,

only for the selected month. If you need the average of the last 12 months then

Measure 2 = 
VAR _MaxDate = MAX('Date'[Date])
RETURN
CALCULATE([Measure],
    FILTER(ALL('Date'),
          'Date'[Date]<= _MaxDate && 'Date'[Date] >_MaxDate - 365
      ) )/ 12

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sagarsahoo_123]

Hello @mangaus1111 ,

Thanks a lot for your timely support. I was verifiying randomly some data for this, but found the number is mismatched when i am selecting customers in Slicer. 

For example in the above image, the Total Average of work time < 6hours is for 5 users but in the Card Display it is showing only 1.

Can you please suggest me if i need to modify anything in the code.

Note:-i am using the same code what you have given to me.....

Here is the code i am using for this:--

Code 1:---

Low_WorkTime_HC =

VAR _MaxDate = MAX('FY_Calendar'[Date])

RETURN

CALCULATE([WorkTime_LowPerformer],

    FILTER(ALL('FY_Calendar'[Date]),

          'FY_Calendar'[Date]<= _MaxDate && 'FY_Calendar'[Date] >_MaxDate - 365

      ) )/ 12

Code 2:----

WorkTime_LowPerformer = COUNTROWS(

FILTER(

ADDCOLUMNS(

  SUMMARIZE(VW_NIPPON_PRODUCTIVITY,

            VW_NIPPON_PRODUCTIVITY[PERSONAL_ID],

            'FY_Calendar'[Year],

            'FY_Calendar'[Month]

            ),

         "@Hours",CALCULATE(SUMX(VW_NIPPON_PRODUCTIVITY,VW_NIPPON_PRODUCTIVITY[WT]))

         ),

     [@Hours] < 6 && [@Hours] > 0

     )

)

Kindly help me to find the expected result for this....

Regards,

Sagar

[sagarsahoo_123]

Hi @mangaus1111 ,

Can you please help to get the desired result as using the code it is not coming correctly which i posted in the above. Please help if possible.

Regards,

Sagar

[sagarsahoo_123]

Thanks a lot for your extended support on this..Really helped..

Regards,

Sagar

[sagarsahoo_123]

Hello @mangaus1111 ,

Thanks a lot for your quick support. just one quick check on this. Will this give me the result only for the Selected month in the Slicer or is it going to give me the average of  past 12 months Work TIme from the slicer slection!

Please help me to clarify this, because the table is showing the months of last 12 months of the  selected from slicer.

Regards,

Sagar

[sagarsahoo_123]

Hello Team,

Thanks for your quick support. Here i am attaching the PBIX file.

https://1drv.ms/u/s!AqNLfMSghxwyiGfJsfZ8GQtrrqWF?e=MWkbUZ

Please see the 1st page of it "Duplicate of Details" page. In this if you look at i have Peronal ID and their Work time for respective months. And in the end there is a total Work time for each user. I need to get the count of users who are clocking less than 6 hours in this Total.

Please help in getting the required data.

Regards,

Sagar

[Anonymous]

Hi @sagarsahoo_123 ,

It seems that I have no proper permission to your shared file. Could you please grant me the access to it? Thank you.

Best Regards

[sagarsahoo_123]

Hello,

Please check now with the below link.

https://1drv.ms/u/s!AqNLfMSghxwyiGfJsfZ8GQtrrqWF?e=YorLdm

Regards,

Sagar

[Anonymous]

Hi @sagarsahoo_123 ,

You can create a measure as below to get it and put it on the card visual:

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[PERSONAL_ID] ),
    FILTER ( 'Table', [Total number of users] < 6 )
)

If the above one can't help you get the desired result, please provide some sample data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards