Filtering on SUMMARIZECOLUMNS possible ?

[cplesner]

I’m trying to get the “Sales declining Amount” per Customer Concern which is the sum in difference in Sales between last 12 months and last 12 months last year for those cases where the percentage drop when comparing last 12 month vs last 12 month last year is dropping more than 5% pr Customer Concern Group (which is a grouping attribute of Customers in the Customer table) per Store Country (which is a grouping attribute of stores).

Fact table “Sales Fact” holds sales amount pr date, per customer, per store.

Dropping sales (lower than <-5%Chg per Concern/Store Country):=
CALCULATE(
[Sales(Last 12 Mth vs LY Diff)],
FILTER(
SUMMARIZECOLUMNS(
'Customer'[Customer Concern Group],
'Geography'[Store Country],
"Diff_Percent",SUMX(
'Sales Fact',
DIVIDE([Sales(Last 12 Mth vs LY Diff)],[Sales(Last 12 Mth LY)])
)
),
[Diff_Percent]<-0.05
)
)

Any ideas ?

[v-ljerr-msft]

Hi @cplesner,

Could you try the formula below to see if it works in your scenario? 

Dropping sales (lower than <-5%Chg per Concern/Store Country): =
SUMX (
    FILTER (
        SUMMARIZECOLUMNS (
            'Customer'[Customer Concern Group],
            'Geography'[Store Country],
            "Diff_Sales", [Sales(Last 12 Mth vs LY Diff)],
            "Diff_Percent", SUMX (
                'Sales Fact',
                DIVIDE ( [Sales(Last 12 Mth vs LY Diff)], [Sales(Last 12 Mth LY)] )
            )
        ),
        [Diff_Percent] < -0.05
    ),
    [Diff_Sales]
)

Regards

[cplesner]

Hi v-ljerr-msft

I get this same error.

Calculation error in measure 'Metrics'[Customer Churn (<-5% Chg vs 12 Mth Diff)]: SummarizeColumns() and AddMissingItems() may not be used in this context.

[v-ljerr-msft]

Hi @cplesner,

I just verified that the following similar formula works fine for me with the latest version of Power BI Desktop(2.55.5010.641 64-bit (February 2018)).

Measure 4 =
SUMX (
    FILTER (
        SUMMARIZECOLUMNS (
            Locations[Country],
            Products[Category],
            "abc", [Total Sales],
            "efg", SUMX ( Sales, Sales[Units] )
        ),
        [efg] > 0
    ),
    [abc]
)

Could you share a dummy pbix file(with just some mock data) which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[cplesner]

Hi @v-ljerr-msft

Attached is error measue workbook sample

https://aggregatedk-my.sharepoint.com/:f:/g/personal/cpl_aggregate_dk/Ellz9tWtwghDmVfkxGR2uO0BBkjHTG...

[v-ljerr-msft]

Hi @cplesner,

I may find a workaround in this scenario. 

First, use the formulas below to create two calculate columns in Fact table.

Trade = RELATED(Trade[Trade])

CustomerGroup = RELATED(Customer[CustomerGroup])

Then use SUMMARIZE function instead.

SUMMARIZECOLUMNS1 = SUMX (
    FILTER (
        SUMMARIZE('Fact',
            'Fact'[Trade],
            'Fact'[CustomerGroup],
            "Diff_Sales", [FFE Loaded (Last 12 Mth vs Last 12 Mth LY Diff)],
            "Diff_Percent", SUMX (
                'Fact',
                DIVIDE ( 'Fact'[FFE Loaded (Last 12 Mth vs Last 12 Mth LY Diff)],[FFE Loaded (Last 12 Mth LY)] )
            )
        ),
        [Diff_Percent] < -0.05
    ),
    [Diff_Sales]
)

Regards

[cplesner]

Hi @v-ljerr-msft

Thanks for the feedback. The calculation doesn't return any values (see shared pbix) One thing to notice is that the user will select a month (ex Feb 2018), but the summarize needs to be based on the combinations availible for the last 24 months (Last 12 Month and Last 12 Months LY). There might be combinations of TradeDir/CustomerGroup in ex 2016 that are not in Feb 2018.

I'm trying to get my head around using variables to store a temp table with UniqueMonth, TradeDir, CustomerGroup, Amount for L12M, Amount for L12M Diff and then somehow return a calculation filtering on the month selected and then basically just showing the summed value (L12M Diff) for those combinations of the selected unique month where the L12M Diff% is below -5%.

Performance is key as the model is quiet large

[cplesner]

I can use below if only I somehow could get the Last 24 Months of combinations of TradeDir/CustomerGroup included in the summarize. It only shows sum of [Diff] amount for those TradeDir/CustomerGroup combinations that have an amount in the selected month (Feb 2018)

Churn :=
CALCULATE (
    [Diff];
    FILTER (
        SUMMARIZE ( 'Fact'; 'Fact'[TradeDir]; 'Fact'[CustomerGroup] );
        DIVIDE ( [Diff]; [L12MLY] ) < -0,05
    )
)

[cplesner]

Hi v-ljerr-msft

Sounds good. Internally we are only running december version, so i'll try and make a version on my own. One thing i noticed is that that the SUMMARIZECOLUMNS is running in the current date context.

Ex. if i have the following:

[Total Sales] = SUM('Sales'[Sales])

[Total Sales SPLY] = CALCULATE([Total Sales];SAMEPERIODLASTYEAR ('Date'[Date])))

[Total Sales Diff] = [Total Sales]-[Total Sales SPLY]

[Total Sales Diff%] = DIVIDE([Total Sales Diff];[Total Sales SPLY])

Measure 4 =
SUMX (
    FILTER (
        SUMMARIZECOLUMNS (
            Locations[Country],
            Products[Category],
            "abc", [Total Sales Diff],
            "efg", SUMX (Sales, [Total Sales Diff%] )
        ),
        [efg] < 0.05
    ),
    [abc]
)

So when in PowerBI selects Month= February 2018 it summarizes the combinations of Country and Category for [Total Sales] in February 2018, meaning that it will elave out the potential combinations of Country and Category for February 2017 (the SPLY measure used for the [Total Sales Diff] calcuation. In other words i don't get the [Total Sales Diff] for those combinations of Country and Category that were in February 2017 that was not in February 2018 - they are just left out of the SUMMARIZECOLUMNS.

In the current calculation i can solve it by using VALUES instead of SUMMARIZE, but it heavy on the ressources. Any idea of how to include that in the SUMMARIZECOLUMNS ?

[cplesner]

Hi @v-ljerr-msft

I did a little more testing and got latest PowerBI software on another machine.

The model is not a PowerBI model but a SSAS Tabular model running SQL Server 2017 CU4. I can create the measure without warnings and deloy it, but when connecting live from PowerBI (February 2018) i get the error:

Calculation error in measure 'Metrics'[Negative Churn v2 (12 Mth Compare)]: SummarizeColumns() and AddMissingItems() may not be used in this context.

I have send a Frown 🙂

[parry2k]

Do you have calendar dimension in your model? If not then do add calendar dimension, there are many posts on how to create on?

- create two measures, one for last 12 months and one for parallel 12 months (you can palledperiod function to get last 12month sales) and this will get you going.

Critical part is to have calendar dimension in your model.

[cplesner]

Hi parry2
The time calcs are working fine. It's the filtering on a summarized level that I have issues with.