I have a pivot table summarizing actual sales and budget, with a calculated field for variance that is Actuals - Budget. My table also has various columns, like region, sales manager, sales person, etc.
In my pivot table, if I bring in multiple levels, such as sales manager and sales person, it calculates the variance at the subtotal level, rather than summing up the calculated values beneath it. The reason is that my variance is going to be an absolute value, so say a given sales manager has sales people under him who have a -5 variance followed by a +5 variance, this needs to add up to 10, not netting off and being 0.
I can accomplish this using SUMX, such as SUMX(Values([Sales Person]),[Variance]) but then if I re-arrange my row labels, it doesn't necessarily apply any more. I need it to be dynamic so that basically every subtotal within the pivot table sums the rows underneath it rather than calculating the variance.
Is there a way to do this?
Thanks
In my pivot table, if I bring in multiple levels, such as sales manager and sales person, it calculates the variance at the subtotal level, rather than summing up the calculated values beneath it. The reason is that my variance is going to be an absolute value, so say a given sales manager has sales people under him who have a -5 variance followed by a +5 variance, this needs to add up to 10, not netting off and being 0.
I can accomplish this using SUMX, such as SUMX(Values([Sales Person]),[Variance]) but then if I re-arrange my row labels, it doesn't necessarily apply any more. I need it to be dynamic so that basically every subtotal within the pivot table sums the rows underneath it rather than calculating the variance.
Is there a way to do this?
Thanks
