Error on SubTotal and GrandTotal calculation

[lucausa75]

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

[amitchandak]

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]

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 · Accepted solution]

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]

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]

Hi,

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

Best Regards,

Giotto

[lucausa75]

v-gizhi-msft 

Thanks for your example.

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

Thanks

[v-gizhi-msft]

Hi,

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

Best Regards,

Giotto

[lucausa75]

@ v-gizhi-msft

I have Excel 2013; do I have to install 2016 version?

About GROUPBY function I have replaced the function GROUPBY with SUMMARIZE; is it the same?

It has the same effect on my report?

Thanks