Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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:
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
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!