Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
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 May 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
72 | |
67 | |
65 | |
43 | |
42 |
User | Count |
---|---|
47 | |
38 | |
28 | |
28 | |
27 |