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] =
[Target_IPD] = 43
[Daily Work Time formatted] =
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]
Solved! Go to 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
hello !
reason why your total [KPI_Target] incorrect is using DISTINCTCOUNT and VALUES in measures [Daily Target] & [Number of Staff]
what should it be?
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
Think its slowly sinking in i've recreated those two measures as variables as:
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
125 | |
79 | |
68 | |
55 | |
55 |
User | Count |
---|---|
200 | |
104 | |
85 | |
80 | |
77 |