Hello all,
I have a situation where I want to show in one pivottable the gross margin by sales rep on the different sales transactions they concluded, as well as the gross margin of the unit to which they belong. Note that the gross margin at sales rep level does not aggregate to the gross margin at unit level, since the gross margin at rep level uses standard costs, whereas the gross margin at unit level uses actual costs.
So i basically want a pivottable by unit and sales rep with in one column the sales rep gross margin and in the second column the unit gross margin (which will be the same for all sales reps in the same unit).
I have attached a sample file, where i managed to get the result i want by adding the unit gross margin from the unit table to the sales rep table (using "Related") and then calculating an average of the unit gross margin. There must be another way (by using filter to get the appropriate context) but i have not found the correct way of doing this.
Many thanks for your help,
Rgds,
Hi Wim,
No file attached. Please try again. Be sure to click the 'start upload' button after selecting your file and then wait for the grey check mark beside the file size before submitting your reply.
Mynda
Hi Mynda,
Apologies - the file was too big hence it did not upload. I have attached a smaller sample file here.
Regards, Wim
Hi Wim,
Thanks for sharing your file. I don't see anything wrong with your approach. Not sure why you think there should be a better way.
Mynda
Hi Mynda,
It just seems strange having to calculate a value through a workaround which is already present in the model. I have seen a textbook example (in Power BI) where a similar granularity issue was addressed using the intersect function. In that example, the granularity difference existed between budget sales data and actual sales data, which must happen in a lot of situations.
I was curious to see whether that solution would work in Excel as well or whether anyone else had solved this problem in a different manner.
Thanks, Wim
Hi Wim,
While the value is in the model, it's not in the model at the level of sales rep, only at the unit level. This means you have to use a workaround/measure to get it at the granularity you want.
I haven't used INTERSECT for this, but my feeling is that it's not simpler to use because you need to create two virtual tables for INTERSECT to compare and that's just more code 🙂
Mynda
