Percentage Category for the Employee count - Bar Chart

[vinaydavid]

Hi Experts,

I have a scenerio where the customers needs the user interaction with the bar chart.

The data looks like below, and I am after finding the count of Employees falling in the respective 'Percentage categories' based on the Percentage calculation.

LogDate	Name	Hours	Prod/ Non-prod	Supervisor
22/11/2019	A	1	Non-Work Order	Sup_Name
22/11/2019	A	1	Non-Work Order	Sup_Name
22/11/2019	A	2.5	Non-Work Order	Sup_Name
22/11/2019	A	1.5	Non-Work Order	Sup_Name
21/11/2019	A	3.5	Work Order	Sup_Name
21/11/2019	A	1.5	Work Order	Sup_Name
20/11/2019	A	0.5	Work Order	Sup_Name
20/11/2019	A	4.5	Work Order	Sup_Name
19/11/2019	A	1	Work Order	Sup_Name
19/11/2019	A	1	Work Order	Sup_Name
19/11/2019	B	1.5	Non-Work Order	Sup_Name
19/11/2019	B	2	Non-Work Order	Sup_Name
14/11/2019	B	0.5	Non-Work Order	Sup_Name
14/11/2019	B	2	Work Order	Sup_Name
13/11/2019	B	1	Work Order	Sup_Name
13/11/2019	B	1.5	Work Order	Sup_Name
13/11/2019	B	1	Work Order	Sup_Name

Calculation: workorder / total (workorder + Non-work order)  --> for Employee A -> 12/18 = 67%

                                                                                                    --> for Employee B -> 5.5/9.5 = 58%

In the bar chart,

60% - 80% -----> 1

40% - 59% -----> 1

Something like this,

And Customers needs this visual to be interactive, to be able to click on, for example "<50%" bar to analyze further.

I have used the below 'calculated column' to try to achieve this, but somehow I am getting the count too high and inaccurate values. (Also not sure if this approach is the right way of doing)

VAR __NUM = CALCULATE (

SUM ( 'Time Tracker Data'[Hours] ),

'Time Tracker Data'[Productive/ Non-productive] = "Work Order"

)

VAR __DEN = CALCULATE (

SUM ( 'Time Tracker Data'[Hours] ),

ALLEXCEPT('Time Tracker Data','Time Tracker Data'[Name],'Time Tracker Data'[LogDate])

)

VAR __PERC =

DIVIDE (__NUM,__DEN,0)

RETURN

IF (

__PERC > 0

&& __PERC <= 0.050,

"<50%",

IF (

__PERC > 0.050

&& __PERC <= 0.084,

"50% - 84%",

IF ( __PERC > 0.084 && __PERC <= 0.099, "85% - 99%", "100%" )

)

)

Please help!

Thanks for your help in advance!

vinaydavid

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[vinaydavid]

@Ashish_Mathur  Thanks for the inputs and file attachement.

The need is to have interactivity between visuals for further analysis.  

Like for example, if I change the logdate using a slicer, I might get 2 categories. Say <50% and 50% - 80%.

Then, we click on the <50% line item to see further details in other visuals to see leaves, meetings etc.,  

This bucket somehow has to link to the data, to see which Employee (perhaps other fields as well) corresponds to <50% for example?

Can this be achieved? 

[Ashish_Mathur]

Hi,

My solution should work.  Have you tried it?

[vinaydavid]

Hi @Ashish_Mathur 

Thanks for the solution provided.

The approach of creating bucketted values was new for me. Thanks for that :-).

As it is, gives the desired result.

However, I noticed that, this gives the count of employees under a category, and will not be interactive as its not connected to any other tables.

Regards,

David

[Ashish_Mathur]

Hi,

You are welcome.  It will cross filter when you click on another visual.

[amitchandak]

First calc should also have ALLEXCEPT('Time Tracker Data','Time Tracker Data'[Name]) ?

As calc are needed at the employee level

Also, refer :https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[vinaydavid]

Thanks @amitchandak  for the reply.

As suggested, When I use ALLEXCEPT for the numerator as well, the result is not as expected.

I am getting multiple 'Percentage' categories (Eg: 10%-30%, 70%-80%,..... etc.,) for a single Employee.

Where I would expect, 1 percentage category for Work Order and 1 for Non-Work order. (later will exclude Non-Work order)

Also, I have a Slicer based on 'Logdate'. But seems that this slicer values are not impacting the Calculated columns.

The results are weird.

Please help.

[vinaydavid]

Some more inputs....

With Measure, I getting the right value....

But I can't plot this onto a bar graph..

But with the use of calculated column, multiple percentage categories are being displyed. No clue, on how the DAX is working.