Aggregate a Calculated Measures

[Anonymous]

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.

[Anonymous · Accepted solution]

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))

[Ashish_Mathur]

Hi,

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.

[Anonymous]

Availability is also a a measure, do you think a simple sum will still work?

[Ashish_Mathur]

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])

[Anonymous]

I agree, for some reason my Power BI desktop on my computers started adding the table name. If you know how to turn it off, please let me know.

[Tutu_in_YYC]

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 this

[Anonymous]

I'm also aware I will need multiple measures, I just can't get it to count each time is see a specific value.