Issues with calculating total score when multiple periods are selected.

Anonymous — Fri, 23 Feb 2024 13:51:58 GMT

Hello,

I have a complex power bi report that I use for team member preformance in my department. We have a total preformance metric that I have calculated using a measure. The main report time filter is using specific period numbers. I had an incorrect understanding of how measures and filters worked. And was under the assumption that the code was calculating averages. This is not the case.

When a single period is selected, the calculations are correct. But if multiple periods are selected, the calculations are not. I need the measure to average to total score for any of the selected periods. But I have no idea how to do this. Below is a screenshot for reference.

All the calculations for Analyst Total Metric Calc (far left columns) are correct. But the total needs to be an average over the periods selected (so (1.95 + 2.70 + 3.30)/3) The correct value for the above table should be 2.65.

Trying to write an average measure returns the same value.

Analyst NEW Total Metric Calc = AVERAGEX('z_Score-TotalMetrics', [Analyst Total Metric Calc])

I tried to calculate something that would count the number of distinct periods and then divide the total metric by those periods. But it was doing the total metric calculation THEN dividing.

I thought maybe I needed to make a calculated table where these values can live so I can average them. But I cannot for the life of me figure it out. Also, these reports need to refresh automatically, and I've had some errors where refreshes fail using calculated tables.

I think the table would need to be

Person	Period Number	Total Score
Person A	2023-11	1.95
Person A	2023-12	2.7
Person A	2023-13	3.3
Person B	2023-11	3.3
Person B	2023-12	3.85
Person B	2023-13	3.6

So I could then do AVERAGE (Total Score).

But I'm at my wits end. I was trying to give an example/sample report but it's super complicated and too big to attach here. Please let me know if there's more information that's needed.

Below are the measures and tables involved. My dates table has the period number and period index.

Analyst Total Metric Calc = ---Correct as long as only one period is selected in the filter

VAR _QA = [QA # Metric] * [QA Weighted %]

VAR _L1Resolve = [L1 Resolve # Metric]*[L! Resolve Weighted %]

VAR _TTR =[L1 Time To Resolve # Metric] * [TTR Weighted %]

VAR _KBA = [KBA Use % # Metric]*[KBA Weighted %]

Var _Handle = [Handle Time # Metric]*[Handle Time Weighted %]

VAR _Attendance = [Attendance # Metric]*[Attendance Weighted %]

VAR _Adherence = [Adherence # Metric]*[Adherence Weighted %]

VAR _MinIndex = MIN('Filter-Week Number'[Period Index])

VAR _TotalMetricCalc = CALCULATE(IF(_MinIndex>31, _QA + _L1Resolve + _TTR + _KBA + _Handle + _Adherence + _Attendance, DIVIDE([QA # Metric]+[Adherence # Metric]+ [Attendance # Metric]+[L1 Resolve # Metric]+[L1 Time To Resolve # Metric]+[Handle Time # Metric],6 )))

RETURN

ROUND(_TotalMetricCalc,2)

Period Slicer is just the Period Number with no additonal filters Below is the table for the selected range in the above screenshot.

Period Number	Period Start Date	Period End Date	Fiscal Year	Fiscal Year Start date	Fiscal Year End Date	Period Index	Year Index
2023-11	11/5/23 0:00	12/2/23 23:59	2023	1/29/23 0:00	1/27/24 0:00	36	2
2023-12	12/3/23 0:00	12/30/23 23:59	2023	1/29/23 0:00	1/27/24 0:00	37	2
2023-13	12/31/23 0:00	2/3/24 23:59	2023	1/29/23 0:00	1/27/24 0:00	38	2

Re: Issues with calculating total score when multiple periods are selected.

some_bih — Tue, 27 Feb 2024 06:26:17 GMT

Hi Anonymous

According to formulas, variable _MinIndex use MIN function, this could be main reason you get only one value as min in your case could be only one value.

It is hard to spot your issue, still Try v2 below

Analyst Total Metric Calc v2=
VAR _QA = [QA # Metric] * [QA Weighted %]
VAR _L1Resolve = [L1 Resolve # Metric]*[L! Resolve Weighted %]
VAR _TTR =[L1 Time To Resolve # Metric] * [TTR Weighted %]
VAR _KBA = [KBA Use % # Metric]*[KBA Weighted %]
Var _Handle = [Handle Time # Metric]*[Handle Time Weighted %]
VAR _Attendance = [Attendance # Metric]*[Attendance Weighted %]
VAR _Adherence = [Adherence # Metric]*[Adherence Weighted %]

VAR _MinIndex = SELECTEDVALUE('Filter-Week Number'[Period Index])

VAR _TotalMetricCalc = CALCULATE(
IF(_MinIndex>31, _QA + _L1Resolve + _TTR + _KBA + _Handle + _Adherence + _Attendance, DIVIDE([QA # Metric]+[Adherence # Metric]+ [Attendance # Metric]+[L1 Resolve # Metric]+[L1 Time To Resolve # Metric]+[Handle Time # Metric],6 )))

RETURN
ROUND(_TotalMetricCalc,2)

topic Re: Issues with calculating total score when multiple periods are selected. in DAX Commands and Tips

Issues with calculating total score when multiple periods are selected.

Re: Issues with calculating total score when multiple periods are selected.