SUMMARIZE NOT WORKING AS NEEDED

[avalonds]

Hoping to get help with the following issue:

I want a measure that creates virtual table to return all products and customer ids in the first two columns (ignore any slicers) and then take Current year units measure and prior year units measure (based on date slicer on dashboard) and the last column subtracts the two measures. The result is a sumx on the subtracted column.

I know the simple solution is to just use calculate but given this is part of a bigger measure I'm working on, it needs to be done using sumx and summarize as I need to perform a computation on every row of the virtual customer-product pair.

The issue I'm facing is that if a user selects a fiscal year 2017 (current year) as a slicer, the product and customers showing in the virtual table are only 2017. Hence if there were units in 2016 and not in 2017 it will ignore this and I don't want it to. It should show all products and customers and then use the filter context to get current year (2017) units and prior year (2016) units for each product customer grouping. Then subtract the two columns on a row level and finally add the final result. Help is much appreciated.

I have a pbix file with the issue. How do I insert a pbix file as an attachment here?

'''dax formula help

//given the page slicer is set to year 2017, it only gives me a list of 2017 products and customers. Any customers in 2016(prior year) is ignored. I don't want it to ignore those customers.

FormulaHelp = SUMX ( ADDCOLUMNS ( SUMMARIZE ( 'Sales_data', Customer_Data[Customer ID], Product_Data[Product ID] ), "YOY_Units", CALCULATE ( [CurrentYear_Units] - [PriorYear_Units] ) ), [YOY_Units] ) '''

[Ashish_Mathur · Accepted solution]

Hi,

Selected 2017 in the Year slicer.  The Year slicer should be from the Calendar Table.  There should be a relationship from the Date column of the Sales Table to the Date column of the Calendar Table.  Try this measure

Measure1 = SUMX(SUMMARIZE(CALCULATETABLE(GENERATE(VALUES(Customer_Data[Customer ID]),VALUES(Product_Data[Product ID])),ALL(Calendar)),[Customer ID],[Product ID],"ABCD",SUM('Sales data'[Amount]),"EFGH",CALCULATE(SUM('Sales data'[Amount]),PREVIOUSYEAR(Calendar[Date]))),[ABCD]-[EFGH])

[avalonds]

 So I'm using the following code per your suggestion. Is there a way I can modify the code to make it less processing intenstive. My customer table is about 50K rows and product is about 70K rows making the "GENERATE TABLE" option very computation intensive. Maybe using cross join, etc?

Measure =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            CALCULATETABLE (
                GENERATE (
                    VALUES ( Customer_Data[Customer ID] ),
                    VALUES ( Product_Data[Product ID] )
                ),
                ALL ( Date_Data )
            ),
            [Customer ID],
            [Product ID]
        ),
        "YOY_Units", CALCULATE ( [CurrentYear_Units] - [PriorYear_Units] )
    ),
    [YOY_Units]
)

[Ashish_Mathur]

Hi,

I do not know how to make the formula more efficient.  Someone else will help you.

[avalonds]

Thanks @Ashish_Mathur . I think I figured it out. Rather than generate a table of two dimension tables and recreate every pairing of customer-product relationship, I just changed the view to the fact table that had both id's and used the filter all date context on that. In this way it doesn't recreate all pairing combinations but rather just the ones that already exist in the fact sales table.

Measure =
SUMX (
    ADDCOLUMNS (
            CALCULATETABLE (
                SUMMARIZE(
                    Fact-Sales,
                    [Product ID],
                    [Column ID]
                ),
                ALL ( Date_Data )
            ),
        "YOY_Units", CALCULATE ( [CurrentYear_Units] - [PriorYear_Units] )
    ),
    [YOY_Units]
)

[avalonds]

You sir are a genius! The GENERATE inside a CALCULATETABLE is exactly the solution I needed!

[Ashish_Mathur]

Thank you for your kind words.

[v-cherch-msft]

Hi @avalonds 

 You can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.Check if below measure could help.

Measure =
SUMX (
    SUMMARIZE (
        'Sales_data',
        Customer_Data[Customer ID],
        Product_Data[Product ID],
        "YOY_Units", CALCULATE ( [CurrentYear_Units] - [PriorYear_Units] )
    ),
    [YOY_Units]
)

Regards,