Forum Discussion

Rohit_dutta's avatar
Rohit_dutta
Frequent Visitor
4 years ago

counting measure

I have calculated 4 measure from my table as below

 

Scheduled_Effort = Var SE = calculate(sum('Resource Utilization Query'[scheduled_effort]), filter('Resource Utilization Query', 'Resource Utilization Query'[period_start] > today()))
return
if(isblank(SE), 0 , SE)
Remaining_Capacity = CALCULATE(sum(Capacity_Measure[capacity]), filter(Time_Dimension, Time_Dimension[period_start] > today()))
Scheduled_Utilization = [Scheduled_Effort] / [Remaining_Capacity]
 Utilization_Category = if('Resource Utilization Query'[Overall_Utilization] < 1, "Underutilized", if('Resource Utilization Query'[Overall_Utilization] = 1, "Proper Allocated", "Overal Load"))
 
now I want to count all the people falls under utilization_category, like below table
 
Utilization_CategoryCount
Underutilized5
Over Load10
Proper Allocated15

 

I have tried many combination in switch masure but it did not work. one example below:-

 

Switch_measure_category =
var selected = SELECTEDVALUE(Category[Category])
var SU = DIVIDE([Scheduled_Effort], Capacity_Measure[Remaining_Capacity])
 
return
SWITCH(True(),
selected = "Over Load", CALCULATE(DISTINCTCOUNT('Resource Utilization Query'[Resource_Name]), filter(Capacity_Measure, Not ISBLANK(Capacity_Measure[Remaining_Capacity])), filter('Resource Utilization Query', SU >1)),
selected = "Underutilized", CALCULATE(DISTINCTCOUNT('Resource Utilization Query'[Resource_Name]), filter(Capacity_Measure, Not ISBLANK(Capacity_Measure[Remaining_Capacity])), filter('Resource Utilization Query', SU <1)),
selected = "Proper Allocated", CALCULATE(DISTINCTCOUNT('Resource Utilization Query'[Resource_Name]), filter(Capacity_Measure, Not ISBLANK(Capacity_Measure[Remaining_Capacity])), filter('Resource Utilization Query', SU =1)))
 

3 Replies