cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BrendaA Visitor
Visitor

Measure: sumx with summarize

I  have a table with following fields

2019-09-23 15_04_20-Example PowerBi - Power BI Desktop.png

 

 

 

 

 

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.

2019-09-23 15_22_25-Example PowerBi - Power BI Desktop.png

 

 

 

 

 

 

 

 

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

1 REPLY 1
Super User IV
Super User IV

Re: Measure: sumx with summarize

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.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors