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 not correct, in fact my "UNIT_QTY" column should contains the values in "Average of QTY" column which, for the subtotal and total, contains the summatory of the average above.

In order to fix the problem I did something with this measure: SUMX(DISTINCT(TABLE[QTY]),TABLE[QTY]) but obviously it misses the duplicated vales in the calculation; in fact in the second section with AAAA==>PHONE==> I will have (76-38) instead of (38+38).

Please, could you help me on solving this issue?

 

Thanks

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

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Try like

    AverageX(summarize(Table,table[Channel],table[Service],Table[Model],table[Code],"_1",Sum(Table[Qty])),[_1])

     

    Remove/Add Group by in summarize as per need

    • lucausa75's avatar
      lucausa75
      Frequent Visitor
      6 years ago

      Thanks for your quick reply.

       

      I don't understand this "_1" in your formula: AverageX(summarize(Table,table[Channel],table[Service],Table[Model],table[Code],"_1",Sum(Table[Qty])),[_1])

       

      Please could you explain?

       

      If I would like to have this grouping (see values in column E) can I use your previous formula?

       

       

      Thanks

      • v-gizhi-msft's avatar
        v-gizhi-msft
        Icon for Community Support rankCommunity Support
        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