Forum Discussion
Aggregate a Calculated Measures
Hello everyone. I have created a measure that does exactly what I need it to do which is show an image based on the number.
Availiability KPI* = SUMX(ResourceTimephasedDataSet, ResourceTimephasedDataSet[Availability])Availiability KPI =
var KPIText =
SWITCH(TRUE(),
ResourceTimephasedDataSet[Availiability KPI*] <= -101, "Way Overallocated",
ResourceTimephasedDataSet[Availiability KPI*] > -101 && ResourceTimephasedDataSet[Availiability KPI*] <= -50, "Overallocated",
ResourceTimephasedDataSet[Availiability KPI*] > -50 && ResourceTimephasedDataSet[Availiability KPI*] <= -20, "Partially Overallocated",
ResourceTimephasedDataSet[Availiability KPI*] > -20 && ResourceTimephasedDataSet[Availiability KPI*] <= 20, "OK",
ResourceTimephasedDataSet[Availiability KPI*] > 20 && ResourceTimephasedDataSet[Availiability KPI*] <= 50, "Improve",
ResourceTimephasedDataSet[Availiability KPI*] > 50 && ResourceTimephasedDataSet[Availiability KPI*] <= 100, "Plan",
ResourceTimephasedDataSet[Availiability KPI*] >= 101, "Missing Work",
"OK"
)
var KPIIcon =
SWITCH(TRUE(),
KPIText = "Way Overallocated", UNICHAR(127761),
KPIText = "Overallocated", UNICHAR(127765),
KPIText = "Partially Overallocated", UNICHAR(127763),
KPIText = "OK", UNICHAR(9989),
KPIText = "Improve", UNICHAR(11088),
KPIText = "Plan", UNICHAR(128221),
KPIText = "Missing Work", UNICHAR(9940)
)
var counticon =
IF( [Availiability KPI*] <> BLANK(),
FORMAT( CALCULATE( DISTINCTCOUNT(ResourceTimephasedDataSet[Resource Name]), FILTER(ResourceTimephasedDataSet, ResourceTimephasedDataSet[Availiability KPI*] >= 1)), "#"),
BLANK())
return IF(HASONEVALUE(ResourceTimephasedDataSet[Resource Name]), KPIIcon, counticon)
What I want to show now is a count for each month, how many times the green check & etc.... shows up.
| 2022-03 | 2022-04 | 2022-05 | ||
| Way Overallocated | 0 | 1 | 1 | |
| Overallocated | 0 | 0 | 0 | |
| Partially Overallocated | 3 | 2 | 0 | |
| Ok | 2 | 0 | 1 | |
| Improve | 0 | 0 | 1 | |
| Plan | 0 | 2 | 0 | |
| Missing Work | 0 | 1 | 3 |
Any help would be greatly apprecaited, I've been trying to figure it out for the past 2 days.
I found my solution, took me 4 days to come up with this but I got it. I also had to do it for each case. I hope this will be able to help someone else in the future.
Availiable KPI Ok = COUNTX( DISTINCT(ResourceTimephasedDataSet[Resource ID]), IF(ResourceTimephasedDataSet[Availability KPI*] > -20 && ResourceTimephasedDataSet[Availability KPI*] <= 20, 1))
7 Replies
I found my solution, took me 4 days to come up with this but I got it. I also had to do it for each case. I hope this will be able to help someone else in the future.
Availiable KPI Ok = COUNTX( DISTINCT(ResourceTimephasedDataSet[Resource ID]), IF(ResourceTimephasedDataSet[Availability KPI*] > -20 && ResourceTimephasedDataSet[Availability KPI*] <= 20, 1))You are trying to use description that doesnt exist as a table/column i.e Way Overallocated,Overallocated etc..
This means we need to create 1 measure for each of the description.Way Overallocated = How many resource that is way overallocated
Overallocated = How many resource that is overallocated
Partially overallocated = How many resource that is partially overallocated
.....and so on..
Then use these measures in a matrix and put the months in column, and its gonna look like this:
Then turn on "show values as rows"
And then it will look like thisHi,
You do not need the SUMX. A simple SUM function should do.
Availiability KPI* = SUM(ResourceTimephasedDataSet[Availability])I think i canbring about some more improvements. Please share the link from where i can download your PBI file.
Ideally measures hould never be prceeded with the Table name. That is why i suggested a SUM function. If it is a measure, thena SUMX would be required.
Availiability KPI* = SUMX(ResourceTimephasedDataSet,[Availability])
