Forum Discussion
Advocate IIISUMming in a SLICEd MEASURE
Hi,
I have a question which might already be in the forum, but it always seems that my situation is slightly different and non of the answers provided me with an solution. The annoying bit is that it looks so simple, but still gives me a headache. So, here goes...
Main table:
Person Timecard Period Hours
John 12345 2017-1 7
John 13334 2017-2 8
Jim 12223 2017-2 8
...
Working days reference table:
Period Legal Working Days
2017-1 21
2017-2 22
2017-3 20
...
FTE reference table:
Person FTE
John 1
Jim 0.8
Jenny 0.9
I want to calculate for a given period what percentage of actual hours people worked against the possible amount of hours that could be workd in that period (Legal Working Days * 8). I have also apply the FTE to the selected person(s).
What I need is to be able to slice the main table for the period(s) and person(s) and sum the Legal Working Days for the select period(s). Something like: Sum(Hours over period) for John / Legal Working Days * FTE
The solution I was looking into was to get the sum of Legal Working Days for the given period, but whatever I tried, it either provided me the total sum of the Legal Working Days column or just the days from 1 period.
I now throw in the towel and humbly present my case to you guys.
Thanks.
Rudi
Hi all,
it took me a while, but I have managed to solve this issue myself (persistence pays).
The answer is in the use of CALCULATE() and ALLSELECTED() (many thanks to The Definitve Guide to DAX by Alberto Ferrari and Marco Russo).
Example measure : Legal Working Days = Calculate(Sum('CalendarMappingTable'[Legal Working days]);ALLSELECTED('FactTable'[Calendar Month])). This will honnour the selection made by the slicer for the "Calendar Month" field.
You can then use the measure in yoyur calculations.
Bye,
Rudi
3 Replies
v-qiuyu-msftCommunity Support
Hi rudiklein,
You can create a calculated column in Main table to return the Legal Working Hours:
LegalWorkingHours = RELATED(Table2[Legal Working Days])*8
Then you can create a measure:
Percentage = SUM('Table1'[Hours])/SUM(Table1[LegalWorkingHours])*MAX('Table3'[FTE])
Best Regards,
Qiuyun Yu- rudiklein
Hi v-qiuyu-msft,
Thanks very much for the example you've built. It works exactly as I wanted. I was using a similar method myself.However, when I apply your method to my dashboard, it still doesn't work. The calculation for the percentages is still wrong.
It can be explained by the SUM() of the Legal Working Hours (Legal Working Days * 8). For some reason it is always summing more then the Legal Working Hours that are visible on the rows in the table. It appears to be summing the whole column.I went step-by-step through you example and compared to the implementation in my database. It seems the same (I know it isn't otherwise it would work corectly. Right?). It must be so obvious, that I am missing it completely.
I have prepared a anonymized version of my model and made it available. Maybe you could have a quick look to see what I'm missing here and help me out of my misery ;-). My Model
Thanks,
Rudi
- rudiklein
Hi all,
it took me a while, but I have managed to solve this issue myself (persistence pays).
The answer is in the use of CALCULATE() and ALLSELECTED() (many thanks to The Definitve Guide to DAX by Alberto Ferrari and Marco Russo).
Example measure : Legal Working Days = Calculate(Sum('CalendarMappingTable'[Legal Working days]);ALLSELECTED('FactTable'[Calendar Month])). This will honnour the selection made by the slicer for the "Calendar Month" field.
You can then use the measure in yoyur calculations.
Bye,
Rudi