Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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: