June 8, 2018 at 2:52 pm #10018
I have a pivot table such as the following:
Men’s Jeans 100$
Men’s Chinos 50
Men’s Cotton T $40
Sales are entered the lowest category.
I am trying to get the $ to roll up and display at the higher category rows like this using DAX.
Category 1: Men’s 190$
Category 2: Men’s Pants 150$
Category 3: Men’s Jeans 100$
Category 3: Men’s Chinos 50$
Category 2: Men’s Shirts 40$
Category 3: Men’s Cotton T 40$
I have tried this formula, but the formula returns an error:
IF(COUNTROWS ( VALUES ( Table[Category3 ))=1,Table[Sales$],SUMX(Table[Sales$))
Thank you in advance for letting me pick your brain once more.
DanJune 8, 2018 at 3:47 pm #10019
note: categories are in separate columns
Category1 Category2 Category3 Sales$
Men’s Men’s Pants Men’s Jeans 100$
Men’s Men’s Pants Men’s Chinos 50$
Men’s Men’s Shirts Men’s Cotton T 40$June 8, 2018 at 4:16 pm #10020
I even tried summarizecolumns, but I get a formula error. Even if it worked it wouldn’t give me what I want – I don’t want to display pivot detail + subtotals, I only want to display the actual value at category3s columns level on pivot and rollups for Category1 column and Category2 column value levels on the pivot.
=SUMMARIZECOLUMNS ( ROLLUPADDISSUBTOTAL ( Table[Category3], “IsCat3Subtotal” ), ROLLUPADDISSUBTOTAL ( ROLLUPGROUP ( Table[Category1], Table[Category2] ), “IsCat1Cat2Subtotal” ), “Total Qty”, Sum( [Sales$] ) )June 8, 2018 at 4:40 pm #10021
Actually ended up showing subtotals at top of group and this seemed to do it, just like can be done in a non-PowerPivot Pivot Table. If this is better suited in DAX, then let me know – however, doing the subtotal at top of group seems much simpler.June 10, 2018 at 1:37 am #10022
- Started: 7
- Replies: 2554
- Total: 2561
I like your idea of using subtotals at the top of the group.
You must be logged in to reply to this topic.