Hi,
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
PS.
I cant share the data due to confidential info.