How to display All items (Months) for a Filtered % subtotal (Employee)?

[Anonymous]

Hello,

I am new to Power BI and trying to work out this problem since past two days. Read some articles and forums but still unable to get around it. Looking for some support on this please.

My requirement seems simple. I need to display all the Employees with below 85% YTD Utilization at first level. And then need to be able to expand the Matrix visual to Month column and display the % Utilization of every month for shortlisted employees, regardless of whether monthly % values are below or above 85%. I am able to get till the Employee part but not able to diaplay all the Month values.

I wrote below code so far leveraging Variables and it works correctly for displaying all the Employees with less than 85% utilization. But all the Months are not dispalyed when Employee column is expanded. Months with value more than 85% do not appear while the ones less than 85% do appear.

Utilization Below 85% = 
VAR Employee_Utilization = 
    SUMMARIZE(
     'Employee','Employee'[Employee Name],"%Utilization",[CalculatedMeasure_%Utilization])
VAR Uti_below_85 = 
    FILTER (Employee_Utilization, [%Utilization] < 0.85)
RETURN
    CALCULATE([CalculatedMeasure_%Utilization], Uti_below_85)

I am not sure if this is the correct approach. How can I get all the Months to appear, regardless of their % utilization values, when Employee column is expanded?

Hope above explanation is understandable.

Thanks in advance,

Nirmit

[Anonymous]

Hi @Anonymous 

Many thanks for working out at your end and for sharing the PBI file. Firstly let me say that this solution does work for what I want to achieve. I just have created one more Measure_Visual on top of your file and it works exactly like I want. Attaching below the expression and results.

Measure_Visual = SWITCH(
SELECTEDVALUE(List[% Utilization]),
"Less than 85%",
    CALCULATE([Calculated Measure_% Utilization], FILTER(Dim_Employee,[Calculated Measure_% Utilization(displayed below at employee level)] <0.85)),
"More than 85%",
    CALCULATE([Calculated Measure_% Utilization], FILTER(Dim_Employee, [Calculated Measure_% Utilization(displayed below at employee level)] >=0.85))
)

 However, I have just one quesiton. I was hoping to achieve this by creating two measures only:
1. Calculated Measure_%Utilization - generic
2. Measure_Visual -  to be displayed in visual showing desired values

With your solution we seem to be ending up with four measures instead. It could also be the case that maybe it did not come out clear with my columns naming in Fact_Hours table in my above post. I displayed two separate columns of Calculated Measure in Fact_Hours just for illustration purpose. It is actually intended to be just one generic measure. Anyway that does not harm. So four measures with your approach looks like below:
1. Calculated Measure_% Utilization(displayed below at employee level)
2. Calculated Measure_%Utilization(displayed below at month level)
3. Calculated Measure_%Utilization
4. Measure_Visual

By any chance, is there a way to achieve this with just two measure approach? Or would that just be unnecessary complication?

Thanks for you reply,

Nirmit

[Anonymous]

Hi @Anonymous ,

I made a sample you can check.

Calculated Measure_% Utilization = IF(HASONEVALUE('Fact_Hours'[Month]),[Calculated Measure_% Utilization(displayed below at month level)],[Calculated Measure_% Utilization(displayed below at employee level)])

Best Regards,

Stephen Tao

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

[Anonymous]

Hi Greg, @Greg_Deckler 

Thanks much for your reply. Sorry that it wasn't clear enough. Please see the sample input data and expected output below.

1. Input data
Dim_Employee

Employee Number	Employee Name
1	A
2	B
3	C

Dim_Time

Date (dd/mm/yy)	Week	Month
01/01/21	W1	JAN-21
02/01/21	W1	JAN-21
03/01/21	W1	JAN-21
...
01/02/21	W5	FEB-21
02/02/21	W5	FEB-21
...
30/04/21	W18	APR-21

Fact_Hours

Employee Number	Month	Available Days	Used Days	Calculated Measure_% Utilization (displayed below at month level)	Calculated Measure_% Utilization (displayed below at employee level)
1	JAN-21	21	21	100%	66%
1	FEB-21	20	10	50%
1	MAR-21	22	20	91%
1	APR-21	22	5	23%
2	JAN-21	21	15	71%	93%
2	FEB-21	20	20	100%
2	MAR-21	22	22	100%
2	APR-21	22	22	100%

2. Expected Output (Matrix visual)

Employee / Month (taken from DIM Tables)	Calculated Measure_% Utilization
1	66%
JAN-21	100%
FEB-21	50%
MAR-21	91%
APR-21	23%

3. Logic
Calculated Measure_% Uilization = SUM([Used Days]) / SUM([Available Days])
Employees with overall calculated utilization below the threshold of 85% at overall level (all months) are required to be displayed in Matrix at top level. At second level, under those employees, all the Months should be displayed regardless of their % utilization at month level.
In above example, Employee 1 has % utilization of 66% which is below the threshold of 85%, thus should be displayed. And all months under it should just get simply displayed (even though JAN-21 and MAR-21 for employee1 is fairly above 85% i.e. 100% and 91%)
Employee 2 overall % uilization is 93% which is above threshold of 85%, hence should not to be displayed at all.
On a side note, I am gonna have another display for Employee above 85% - but that shall follow the same logic (with only 'greater than' operator) thus not including that query in this post.

Hope it is clear enough now. Please let me know if you would need more information on this.

Thanks

Nirmit

[Greg_Deckler]

@Anonymous What is your first level? How do you "identify" it? If you can do that, you can change your FILTER statement (more correctly, set you utilization level in a variable and use that in your filter statement so either .85 or 1. But, not really enough to be specific.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.