Forum Discussion

cplesner's avatar
cplesner
Icon for Helper III rankHelper III
8 years ago

Filtering on SUMMARIZECOLUMNS possible ?

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 dr...
Need Help
v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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. :smileyhappy:

 

Regards

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi cplesner,

     

    I may find a workaround in this scenario. :smileyhappy:

     

    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's avatar
      cplesner
      Icon for Helper III rankHelper III
      8 years ago

      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's avatar
        cplesner
        Icon for Helper III rankHelper III
        8 years ago

        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
            )
        )