Measure: sumx with summarize

Anonymous — Mon, 23 Sep 2019 13:28:03 GMT

I have a table with following fields

What I would like to calculate is the additional online sales in case the shop would sell the same % online per product as all shops together. I was able to generate a summarized table that calculates the "additional online sales"

SummarizedTabel =
ADDCOLUMNS (
    ADDCOLUMNS (
        ADDCOLUMNS (
            SUMMARIZE (
                Sales;
                Sales[Product];
                Sales[Region];
                Sales[Sold by];
                "Sales"; SUM ( Sales[Sales] );
                "Sales Online"; CALCULATE (
                    SUM ( Sales[Sales] );
                    ALLSELECTED ( Sales[Sold by] );
                    Sales[Sold by] = "Online"
                );
                "Sales Store"; CALCULATE (
                    SUM ( Sales[Sales] );
                    ALLSELECTED ( Sales[Sold by] );
                    Sales[Sold by] = "Store"
                );
                "Total shops Online"; CALCULATE (
                    SUM ( Sales[Sales] );
                    ALLSELECTED ( Sales[Sold by]; Sales[Region] );
                    Sales[Sold by] = "Online"
                );
                "Total shops store"; CALCULATE (
                    SUM ( Sales[Sales] );
                    ALLSELECTED ( Sales[Sold by]; Sales[Region] );
                    Sales[Sold by] = "Store"
                )
            );
            "Total shops online vs total"; [Total shops Online] / ( [Total shops Online] + [Total shops store] );
            "Selected shop online vs total"; [Sales Online] / ( [Sales Online] + [Sales Store] )
        );
        "diff total shops vs selected shop"; [Total shops online vs total] - [Selected shop online vs total]
    );
    "additional sales online"; IF (
        [diff total shops vs selected shop] < 0;
        0;
        [Sales] * [diff total shops vs selected shop]
    )
)

But as the original data has more then 1 million rows and I need to apply quite a lot of filters I could like to include the calculation of additional online sales in a measure so that all the report filters get applied (filters based on linked tables). I tried to include the code above in a measure

Addiontional sales =
SUMX (
    ADDCOLUMNS (
        ADDCOLUMNS (
            ADDCOLUMNS (
                SUMMARIZE (
                    Sales;
                    Sales[Product];
                    Sales[Region];
                    Sales[Sold by];
                    "Sales"; SUM ( Sales[Sales] );
                    "Sales Online"; CALCULATE (
                        SUM ( Sales[Sales] );
                        ALLSELECTED ( Sales[Sold by] );
                        Sales[Sold by] = "Online"
                    );
                    "Sales Store"; CALCULATE (
                        SUM ( Sales[Sales] );
                        ALLSELECTED ( Sales[Sold by] );
                        Sales[Sold by] = "Store"
                    );
                    "Total shops Online"; CALCULATE (
                        SUM ( Sales[Sales] );
                        ALLSELECTED ( Sales[Sold by]; Sales[Region] );
                        Sales[Sold by] = "Online"
                    );
                    "Total shops store"; CALCULATE (
                        SUM ( Sales[Sales] );
                        ALLSELECTED ( Sales[Sold by]; Sales[Region] );
                        Sales[Sold by] = "Store"
                    )
                );
                "Total shops online vs total"; [Total shops Online] / ( [Total shops Online] + [Total shops store] );
                "Selected shop online vs total"; [Sales Online] / ( [Sales Online] + [Sales Store] )
            );
            "diff total shops vs selected shop"; [Total shops online vs total] - [Selected shop online vs total]
        );
        "additional sales online"; IF (
            [diff total shops vs selected shop] < 0;
            0;
            [Sales] * [diff total shops vs selected shop]
        )
    );
    [additional sales online]
)

But when I look at the results it is not what I want to see. The total is correct but I am not able to see the additional sales per product en per region.

How can I solve this? Is there another way to get this solved instead of using summarize?

I am using Power BI for just a few weeks, so I would be surprised if there is a much easier way to calculate the additional online sales.

Thanks in advance for your help on this one...

Kind regards

Brenda

Re: Measure: sumx with summarize

Anonymous — Tue, 24 Sep 2019 10:57:32 GMT

GOOD PIECE OF ADVICE: Do not use SUMMARIZE for obtaining figures. Use this function ONLY for getting the combinations of values against which you want to calculate numbers. Instead, use the combination of SUMMARIZE/ADDCOLUMNS.

SUMMARIZE is a very complex function that has many bugs into the bargain. Complex in this context means it's very often doing something different from what you think it should.

If you want to know why you shouldn't do that please refer to the articles on SUMMARIZE at www.sqlbi.com.

Best
D.

topic Measure: sumx with summarize in DAX Commands and Tips

Measure: sumx with summarize

Re: Measure: sumx with summarize