Custom Subtotal  I'm so confused!
Amy Lee Aug 23, 2018 10:49 PMHi there! I have trouble creating subtotals that I could easily do in Excel pivot table but I've been struggling with Tableau.
My project looks like this. First, I have a hierarchy of Div, Dept and Brand. They all have a margin dollar and total sales. I used calculated field in Tableau to calculate a margin %. For this, my formula is: { INCLUDE [Div]: (SUM([Margin]))/(SUM([Total Sales]))}. This works as users may collapse/expand to a div, dept or brand view and the calculation is still correct as I've used the include LOD.
Then, I need to fix LOD the margin % to each dept. Formula: { FIXED [Dept]: SUM([Margin])} / {FIXED [Dept]: SUM([Total Sales])}. I'm showing this in the dashboard now for explaining my methodology, but in the end view, I won't show to the end user.
After that, I want to use each brand's margin % to compare to its dept's margin %. So the formula: { INCLUDE [Div]: SUM([Margin %])/SUM([LOD Fixed Dept])}. I did this for Div as well.
Everything works so far and I'm happy with what I have. The problem comes when I want to do subtotals. Tableau simply add up the column values instead of doing the same calculations. I've read through some suggestions/hacks that use the size function to hack this situation. However, my dashboard actually need to allow user to filter to div, dept or brand that they want, so the size function wouldn't work for me as the number of rows would change based on what the user filters on. I'm not able to find any other resources that can help me with this problem. It's so frustrating! What pivot tables can simply do just can't be done in Tableau... I'd appreciate if anyone can help me with this!!
Attaching my Tableau workbook and my Excel raw data/methodology in the thread.
Thanks,
Amy

Tableau Question 2.twbx 28.1 KB

Tableau Question 2.xlsx 15.3 KB