Dax Measure on Matrix not giving expected totals

[SACooper]

I am planning to turn the above matrix into a sort of heat map - eventually i'll remove the [KPI_Target] value and use that for conditional formatting - however at present the total for [KPI_Target] is wrong - is should be 134 (45+44+45)

[KPI_Target] is a measure: [Days In View] * [Daily Target] * [Number of Staff]

[Days In View] = DISTINCTCOUNT(CompletedItems[ProcessedDate])

[Number of Staff] = COUNTROWS(VALUES(CompletedItems[ProcessedBy]))

[Daily Target]  = 

VAR _testTime = IF(NOT(ISBLANK([Daily Work Time formatted])),[Daily Work Time formatted], "00:00:00")

VAR _hourMarker = FIND(":", _testTime, 1)

VAR _hours = LEFT(_testTime, _hourMarker -1)

VAR _totalDays = ROUNDDOWN(_hours / 24,0)

VAR _remainingHours = _hours - (_totalDays * 24)

VAR _minuteMarker = FIND(":", _testTime, _hourMarker + 1)

VAR _minutes = RIGHT(LEFT(_testTime, _minuteMarker-1), 2)

VAR _seconds = RIGHT(_testTime, 2)

VAR _decimalTime = ((_totalDays * 24 * 60 * 60) + (_remainingHours * 60 * 60) + (_minutes * 60) + _seconds) / (1 * 24 * 60 * 60)

VAR _dayValue = Value("07:15:00")

RETURN

ROUNDDOWN(([Target_IPD] / _dayValue) * _decimalTime,0)

[Target_IPD] = 43

[Daily Work Time formatted] = 

Daily Work Time formatted =

VAR _flexitime = SUM('flexi_FullReport'[Day_TotalTime])

VAR _hours = IF(LEN(_flexitime) = 0,BLANK(), FORMAT(ROUNDDOWN(_flexitime / 60,0),"00"))

VAR _rem_minutes = IF(LEN(_flexitime) =0,BLANK(), FORMAT(_flexitime - (_hours * 60), "00"))

VAR _seconds = FORMAT(0,"00")

VAR _result = IF(AND(ISBLANK(_hours), ISBLANK(_rem_minutes)), BLANK(), _hours & ":" & _rem_minutes & ":" & _seconds)

RETURN

_result

In the above example:

[Days In View]  = 1

[Number of Staff] = 1 (per row) and 3 for the total

[Daily Target]  is correct per row as is the [KPI_Target] 

(I verfied these by adding cards and filtering and by adding the measures to the matrix.)

I'm pretty sure i've dropped the ball somewhere within these measures but after a couple of days i'm not getting much closer to an answer.

[SolomonovAnton]

hello ! 

reason why your total [KPI_Target] incorrect  is using DISTINCTCOUNT and VALUES in measures [Daily Target]  &  [Number of Staff]

[SACooper]

what should it be?

[SolomonovAnton · Accepted solution]

for example take first two rows from you pictures

for row KPI_Target=45 suppose you have data table 

ProcessedDate	ProcessedBy
01.01.2020	1
02.01.2020	2
03.01.2020	1
04.01.2020	2
05.01.2020	4
06.01.2020	5
01.01.2020	1
02.01.2020	2
03.01.2020	1
04.01.2020	2
05.01.2020	4
06.01.2020	5

so [Days In View] =6 and [Number of Staff] = 5 

for row KPI_Target=44 suppose you have data table 

ProcessedDate	ProcessedBy
01.01.2020	1
02.01.2020	2
03.01.2020	1
04.01.2020	2
05.01.2020	7
06.01.2020	5
01.01.2020	1
02.01.2020	8
03.01.2020	1
04.01.2020	2
05.01.2020	4

so [Days In View] =6 and [Number of Staff] = 6

but for total row same data table look like 

ProcessedDate	ProcessedBy
01.01.2020	1
02.01.2020	2
03.01.2020	1
04.01.2020	2
05.01.2020	4
06.01.2020	5
05.01.2020	7
02.01.2020	8

so [Days In View] =6 and [Number of Staff] = 8

I hope this ex[planation will help you for understand 

[SACooper]

Think its slowly sinking in i've recreated those two measures as variables as:

VAR _daysInView = COUNTROWS(VALUES(CompletedItems[ProcessedDate]))

VAR _numberOfStaff = COUNTROWS(VALUES(CompletedItems[ProcessedBy]))

EDIT:  Think its just me over complicating things 

Which now provide the correct total figures -- thinks just go massively sideways when I re-incorporate the daily target.

[SACooper]

VAR _daysInView = COUNTROWS(VALUES(CIVICA_CompletedItems[ProcessedDate]))

VAR _numberOfStaff = COUNTROWS(VALUES(CIVICA_CompletedItems[ProcessedBy]))

--

VAR _flexitime = SUM('flexi UD_FullReport'[Day_TotalTime])

VAR _decimalHours = _flexitime / 60

VAR _dayValue = TIME(7,15,0) * 24

VAR _targetPerHour = _dayValue / [Target_IPD]

RETURN ROUNDDOWN(_decimalHours / _targetPerHour,0)