measure excluding some data catergories wont slice

[Anonymous]

Hi people

I wonder if I could ask for help 

I've calculated hours utilisation using the following measure

Utilisation = [Total Chargeable Time] / [Total Time Less Modifiers]

Its made up of total chargeable time  that looks like this " Total Chargeable Time = CALCULATE(SUM(POT[Hours ]),
FILTER(POT,POT[CHG] = "CHG") " bringing back the total of all time labelled as chargeable.

and 

total time less modifiers that looks like this, Total Time Less Modifiers = CALCULATE([Total Time], POT[Activity ] <> "Annual Leave", POT[Activity ] <> "Maternity Leave", POT[Person ] <> "FD") " bringing back the total time excluding two activity categories and the admin lady.

my problem is that i cant slice this by person , when i put them on a chart  with person on the axis i get this thats not correct, i think what its showing is each persons % of the whole, when what i want is the total utilisation %. it works by all other metrics apart from by person - can anyone explain why - many thanks  

i get this result 

)

[Anonymous]

haha,

sorry to bother everyone - i think i solved it myself, I just excluded the admin lady on a visual level filter and took her out of the measure  and it seems of have worked - would still be interested to know if this can be done with just DAX though 

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

I made one sample for your refernce. Here I updated your formulas as below.

Total Time Less Modifiers1 =
CALCULATE (
    [Total Time],
    FILTER (
        ALLSELECTED ( POT ),
        POT[Activity ] <> "Annual Leave"
            && POT[Activity ] <> "Maternity Leave"
            && POT[Person] <> "FD"
    )
)

Utilisation1 = [Total Chargeable Time] / [Total Time Less Modifiers1]

Then we can get the result as we excepted.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/tkdqlbre0y54tq3/measure%20excluding%20some%20data%20catergories%20wont%20slice.pbix?dl=0

Regards,

Frank

[Anonymous]

thanks frank, thats really useful 

[Anonymous]

haha,

sorry to bother everyone - i think i solved it myself, I just excluded the admin lady on a visual level filter and took her out of the measure  and it seems of have worked - would still be interested to know if this can be done with just DAX though