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.