cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
avalonds Frequent Visitor
Frequent Visitor

SUMMARIZE NOT WORKING AS NEEDED

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: SUMMARIZE NOT WORKING AS NEEDED

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

7 REPLIES 7
Community Support Team
Community Support Team

Re: SUMMARIZE NOT WORKING AS NEEDED

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: SUMMARIZE NOT WORKING AS NEEDED

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 Frequent Visitor
Frequent Visitor

Re: SUMMARIZE NOT WORKING AS NEEDED

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

Super User
Super User

Re: SUMMARIZE NOT WORKING AS NEEDED

Thank you for your kind words.

avalonds Frequent Visitor
Frequent Visitor

Re: SUMMARIZE NOT WORKING AS NEEDED

 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]
)
Super User
Super User

Re: SUMMARIZE NOT WORKING AS NEEDED

Hi,

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

avalonds Frequent Visitor
Frequent Visitor

Re: SUMMARIZE NOT WORKING AS NEEDED

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