Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Pivot Table & DAX



I am trying to create a measure for calculating sales orders variance from total average. by default when i  create a measure for average, the average is calculated for ech subgroup and that is not what i want. what i want is an average recalculated for each group and repeated in each row of the pivot table so that i can measure the variance. 

As you see in the picuture ( Which is in the excel file) , i managed to create this measure ( Last column) and i also brought in the total average into the pivot table so that you fully understand what i mean.

The problem with my measure is that i get the labels for subgroups whre i have no sales in them ( i used All function in the filter argument of the calculate function to get the repetitive average). and therefore i get redundant fields in the chart with no bars in it and that makes my chart and also pivot table Ugly and that is not what i want. 

Do you guys have an idea of how i should create this measure? 

Much Apprecited


I cant share the data due to confidential info.





I think I understand your issue to be you want to hide rows where the value under a pivot table column are zero. 

If that field is your "Total Sales by Age Group", click on the table to show the Pivot Table Fields and drag the feild into the Filters pane. It will appear above the pivot table with (All) in the column next to it- click on the down arrow of that, tick/check the box by "Show Multiple Items" then untick/ uncheck the box by "(blank)". That should do it.



BTW it's much better for us in future if you attach an Excel file with test (not confidential) data- that way, we don't have to recreate your problem to check the solution works. That said, it's hard to see why 5 year old data on TV sales etc. would be that confidential IMHO.
John_Ru (rep: 4922) Nov 18, '21 at 7:02 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login