Target vs Actual not working with filters :-(

Simsy — Tue, 28 Jul 2020 16:31:24 GMT

Hi Guys,

I've been tasked with generating a PowerBi report that compares actual billable hours with a percentage target that needs to be calculated at different levels. I've managed to make work at a single level, but it falls apart when I add more slicers.

The small table below is an extract of a dataset which runs > 100,000 rows

The calculation I'm trying to do, is for eaxmple;

Steve in Month = Jan has booked (128+25+7) 160 hours in total
His target is to have 72% of the total be 'Billable'
128/160 = 80% which is above his target

This simple calculation I can do, by using CALCULATE, SUM & ALLEXCEPT but when I want to see his performance over 2,3 or 4 months the output of my measure makes no sense, likewise if I want to see all the Plumbers in January, it gets worse still and when I add slicers to show the combined result by country/Role/Month.

I think my problem is, I'm trying to calculate the measure too soon . . . each measure needs to be based on the total number of hours booked by each person in each month, but when grouping people together I need to see the 'Cost Type' Hours / Sum('month' hours)

ANY help would be gratefully appreciated.

Thanks

Country	Name	Role	Month	Cost Type	Hours Booked	% Target
England	Steve	Plumber	Jan	Billable	128	72%
England	Steve	Plumber	Jan	unbillable	25	15%
England	Steve	Plumber	Jan	Vacation	7	13%
England	Steve	Plumber	Feb	Billable	112	75%
England	Steve	Plumber	Feb	unbillable	35	15%
England	Steve	Plumber	Feb	Vacation	13	10%
England	Steve	Plumber	March	Billable	119	80%
England	Steve	Plumber	March	unbillable	33	13%
England	Steve	Plumber	March	Vacation	8	7%
England	Jane	Electrician	Jan	Billable	130	72%
England	Jane	Electrician	Jan	unbillable	12	15%
England	Jane	Electrician	Jan	Vacation	30	13%
England	Jane	Electrician	Feb	Billable	100	75%
England	Jane	Electrician	Feb	unbillable	23	15%
England	Jane	Electrician	Feb	Vacation	22	10%
England	Jane	Electrician	March	Billable	116	80%
England	Jane	Electrician	March	unbillable	13	13%
England	Jane	Electrician	March	Vacation	36	7%

Re: Target vs Actual not working with filters :-(

lbendlin — Wed, 29 Jul 2020 11:16:31 GMT

Here is the pedestrian way of doing it. It requires fields to be present in the visual for the measure to work.

%Targ := var n=SELECTEDVALUE(Costs[ Name]) var r=SELECTEDVALUE(Costs[Role]) var m=SELECTEDVALUE(Costs[Month]) var t=CALCULATE(sum(Costs[Hours Booked]),Allselected(Costs),Costs[ Name]=n,Costs[Role]=r,Costs[Month]=m) return divide(sum(Costs[Hours Booked]),t)

That's not optimal if you want flexibility. For that you will want to look at other options like this one which will work regardless of the other filters.

%Targ2 := DIVIDE(sum(Costs[Hours Booked]),CALCULATE(sum(Costs[Hours Booked]),ALL(Costs[Cost Type])))

Greg_Deckler Can you please teach me how to write this without Calculate?

Re: Target vs Actual not working with filters :-(

Simsy — Wed, 29 Jul 2020 09:32:31 GMT

Many thanks lbendlin, the 2nd solution you offered not only solved my problem, but also helped me enhance the report even more 🙂

topic Target vs Actual not working with filters :-( in DAX Commands and Tips

Target vs Actual not working with filters :-(

Re: Target vs Actual not working with filters :-(

Re: Target vs Actual not working with filters :-(