Forum Discussion

lucausa75's avatar
lucausa75
Frequent Visitor
6 years ago
Solved

Error on SubTotal and GrandTotal calculation

Dear all, I have a table like this: and I would like to have a grouping like this:   As you can the column called "UNIT_QTY" gets the correct values but the subtotal and the total are...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try these measures:

    UNIT_QTY =
SUMX (
    GROUPBY (
        'Table',
        'Table'[CHANNEL],
        'Table'[SERVICE],
        'Table'[MODEL],
        'Table'[CODE]
    ),
    CALCULATE ( AVERAGE ( 'Table'[QTY] ) )
)
    AVG PRICE =
SUMX (
    GROUPBY (
        'Table',
        'Table'[CHANNEL],
        'Table'[SERVICE],
        'Table'[MODEL],
        'Table'[CODE]
    ),
    CALCULATE ( AVERAGE ( 'Table'[PRICE] ) )
)

    The result shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto

lucausa75's avatar
lucausa75
Frequent Visitor
6 years ago

Thanks for your feedback!

I have replaced the function GROUPBY with SUMMARIZE to be used in my PowerPivot table and it works

I'm also trying to get the same result by creating a KEY field and for each unique KEY I have to add the PRICE...

I'm new on this DAX functions...

 

So I'm looking for something like: "For each unique KEY take the PRICE"

 

Thanks a lot for your great support!

v-gizhi-msft's avatar
v-gizhi-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi,

 

You can still use GROUPBY function in Excel PowerPivot, see this:

 

Best Regards,

Giotto

  • lucausa75's avatar
    lucausa75
    Frequent Visitor
    6 years ago

     

    Thanks for your example.

     

    This is what happens to me when I add GROUPBY function in my PowerPivot:

     

     

    Thanks

    • v-gizhi-msft's avatar
      v-gizhi-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi,

       

      It is related to the Excel version, please try to update your excel to the latest version.

       

      Best Regards,

      Giotto

      • lucausa75's avatar
        lucausa75
        Frequent Visitor
        6 years ago

        @ v-gizhi-msft