Home Forums Power Pivot Aggregation question

This topic contains 4 replies, has 2 voices, and was last updated by  tomallan 6 months, 1 week ago.

Viewing 5 posts - 1 through 5 (of 5 total)
• Author
Posts
• #10018

dpfish00
Participant
• Started: 6
• Replies: 18
• Total: 24

Hi Tom,

I have a pivot table such as the following:

Sales \$

Men’s

Men’s Pants

Men’s Jeans         100\$

Men’s Chinos       50

Men’s Shirts

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.

Sales \$

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.

Best,

Dan

#10019

dpfish00
Participant
• Started: 6
• Replies: 18
• Total: 24

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\$

#10020

dpfish00
Participant
• Started: 6
• Replies: 18
• Total: 24

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\$] ) )

#10021

dpfish00
Participant
• Started: 6
• Replies: 18
• Total: 24

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.

#10022

tomallan
Keymaster
• Started: 7
• Replies: 2545
• Total: 2552

Dan,

I like your idea of using subtotals at the top of the group.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.