Average of the Average Measure with Group filter

[saud968]

I have a measures

Avg count Closed Cases = [CID]/[PDays]

PDays = SUM(Attendance[Present days])

CID = CALCULATE(COUNT(support__cw_ops_manage_case_merge_temp[ID]), support__cw_ops_manage_case_merge_temp[ClosedCaseFlag] = TRUE())

Now I need the average of Avg count Closed Cases by the group filter per product which 

'Colleague List', 'Colleague List'[BU] further filtered by 

'Colleague List'[Location] IN {"Australia", "United Kingdom", "United States", "Philippines"}).

Now I have tried measure 

AvgClosedCasesByBUAndLocation =

CALCULATE(AVERAGEX(SUMMARIZE('Colleague List', 'Colleague List'[BU]), [Avg count Closed Cases]),  'Colleague List'[Location] IN {"Australia", "United Kingdom", "United States", "Philippines"})

However, when I use the measure  = 

P CC = CALCULATE(DIVIDE([Avg count Closed Cases], [AvgClosedCasesByBUAndLocation])) does not give the correct output. 

https://docs.google.com/spreadsheets/d/1WaHstkif-TEQBxrB9KwBrT0aeo-4b3iz/edit?usp=drivesdk&ouid=1093...

In Excel the above link should give the idea under I2 there is the formula used. 

@Ahmedx @Idrissshatila @amitchandak @Ritaf1983 @Fowmy 

[v-kongfanf-msft]

Hi @saud968 ,

Did @amitchandak  reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best Regards,
Adamk Kong

[saud968]

@v-kongfanf-msft  the solution did not work I have shared further thoughts. Any further help is appreciated

[saud968]

I will be trying it out now and will update

[amitchandak]

@saud968 , Try like

Option1:

CID = CALCULATE(COUNT(support__cw_ops_manage_case_merge_temp[ID]), filter('Colleague List', 'Colleague List'[Location] IN {"Australia", "United Kingdom", "United States", "Philippines"}))

Use the cental Fact in the summarize
Avg = AVERAGEX(SUMMARIZE(Attendance, 'Colleague List'[BU],Attendance[Present days]),[CID])

Option2

P CC =
VAR CurrentAvg = [Avg count Closed Cases]
VAR GroupAvg = CALCULATE(
[AvgClosedCasesByBUAndLocation],
ALLEXCEPT('Colleague List', 'Colleague List'[BU]),
'Colleague List'[Location] IN {"Australia", "United Kingdom", "United States", "Philippines"}
)
RETURN
DIVIDE(CurrentAvg, GroupAvg)

[saud968 · Accepted solution]

Hi @v-kongfanf-msft @amitchandak  i was able to get it fixed by 

KCS Average =

CALCULATE(AVERAGEX(SUMMARIZE('Colleague List', 'Colleague List'[BU]), [Avg Count KCS]),  'Colleague List'[Location] IN {"Australia", "United Kingdom", "United States", "Philippines"}, ALL('Colleague List'[Colleague Name]))

P KCS = [Avg Count KCS]/[KCS Average]*100
and then

Point =

SWITCH(

    TRUE(),

    [P KCS] > 120, 20,

    [P KCS] > 100, 18,

    [P KCS] > 90, 16,

    [P KCS] > 85, 14,

    [P KCS] > 80, 12,

    [P KCS] > 70, 10,

    [P KCS] > 60, 9,

    [P KCS] > 50, 8,

    [P KCS] > 40, 7,

    [P KCS] > 30, 6,

    [P KCS] >= 0, 0,

    BLANK()

)

[saud968]

Hi @amitchandak I tried this 

AvgClosedCasesByBUAndLocation =

CALCULATE(AVERAGEX(SUMMARIZE('Colleague List', 'Colleague List'[BU]), [Avg count Closed Cases]),  'Colleague List'[Location] IN {"Australia", "United Kingdom", "United States", "Philippines"}, ALLSELECTED(support__cw_ops_manage_case_merge_temp[CASE_OWNER_FULL_NAME]))
this worked, however, P CC = CALCULATE(DIVIDE([Avg count Closed Cases], [AvgClosedCasesByBUAndLocation]))  is still giving incorrect output

[saud968]

Hi @amitchandak thank you for the quick response. Unfortunately both options did not work, as you can see in the screenshot above

When I pulled AvgClosedCasesByBUAndLocation in a separate table visual it gave a constant average for BU for example it 1.88 for Automate and Manage as 2.05 so basically I wanted to divide the Avg count Closed Cases/ AvgClosedCasesByBUAndLocation which should give me desired number however, if you check when I do that in the table above the AvgClosedCasesByBUAndLocation gives the same number as Avg count closed cases.