Sum values based on function

[yaman123]

Hi all, 

I need some assistance in creating a measure which sums values based on distinct function. I have the below dataset.

I need to sum the total possible hours per function. So total from below dataset should be 34495 and not 62465. As you can see, there can be more than one function depending on absence type, but the total possible hours should only be counted once towards that function. 

FUNCTION	ABSENCE_TYPE	ABSENCE_HOURS	TOTAL_POSSIBLE_HOURS
A	S	112	480
B		0	320
C		0	1600
D		0	1664
E	M	168	9644
E	S	672	9644
F		0	3648
G		0	480
H		0	1088
I	C	36	9163
I	F	180	9163
I	S	204	9163
J		0	3688
K		0	1600
L		0	1120
			62465

[amitchandak · Accepted solution]

@yaman123 , Try measure like

sumx(summarize(Table, Table[FUNCTION], Table[TOTAL_POSSIBLE_HOURS]), [TOTAL_POSSIBLE_HOURS])

or

sumx(values(Table[FUNCTION]), calculate(Max (Table[TOTAL_POSSIBLE_HOURS])))

[Jihwan_Kim · Accepted solution]

Hi, @yaman123 

Please check the below.

Total Possible Hours =
SUMX (
VALUES ( Data[FUNCTION] ),
CALCULATE ( MAX ( Data[TOTAL_POSSIBLE_HOURS] ) )
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[amitchandak · Accepted solution]

@yaman123 , Try measure like

sumx(summarize(Table, Table[FUNCTION], Table[TOTAL_POSSIBLE_HOURS]), [TOTAL_POSSIBLE_HOURS])

or

sumx(values(Table[FUNCTION]), calculate(Max (Table[TOTAL_POSSIBLE_HOURS])))

[yaman123]

Hi @amitchandak 

Sorry revisiting this again. The numbers i have do not match to what my dataset is. 

From May 2020 - Apr 2021, the total should be 452.080, but when i use the formula sumx(summarize(Table, Table[FUNCTION], Table[TOTAL_POSSIBLE_HOURS]), [TOTAL_POSSIBLE_HOURS]), the total i get is 422.304. 

The total possible hours column sums the hours if there is more than one line per month for that function but i only want to show the distinct value for that function per month. 

Is there any way to write this? 

[yaman123]

Thanks! That seems to have worked