Hi
I have a chart I am trying to create that compares various locations IT cost per device to the average in that region.
I have created a DAX formula that is just dividing the cost by devices and in a simple table that gives me cost per device at each site and at the total region level but what I really want to do is is then calculate the difference between each sites average to the total regional average so I have a variance for each site and can see which sites are deviating from the regional average the most.
I can calculate the Regional Cost per Device in a DAX formula with filters but it continues to change when ever I start filtering at a level lower than that like site.
Is there a way of 'fixing' the average in a DAX formula so it won't filter anymore? Right now I am getting around it by just hardcoding the number into my variance calculation.
I hope that makes sense and thanks for help
Hi Allan,
I cover this in the DAX section of my Power Pivot course, but I see you're not a member of that. You can use the ALL function to remove filters for the regional cost per device calculation e.g
=CALCULATE(SUM(cost per device), ALL(Regions))
Obviously you need to enter the correct table[column] names in the formula above, but hopefully you get the idea.
Mynda
Thank you Mynda... Would you believe it that's the one course I don't have subscribe to... Got query, Tables, pivot tables and Power BI I have been learning the DAX stuff as I go but perhaps I'll go back and look at that with Power Pivot course now.
🙂 no worries. As an existing member you can use this coupon to take 20% off: MOTHVIP
