SUMming in a SLICEd MEASURE

[rudiklein]

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 

[v-qiuyu-msft]

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 · Accepted solution]

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

[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