Ignoring rows with SUMX based on condition

[Anonymous]

Hi everyone!

I'm trying to create a dashboard that lets the user select a date range and will dynamically display the % price increase over that period of time. I want to be able to aggregate these increases by different categories and subcategories, and when aggregating I need to calculate the weighted average based on the sales of each item.

My problem is that sometimes items are missing a price during some periods (mostly because the item is new or discontinued) and when an item is missing it shouldn't be counted when aggregated. I've tried so many different things over the past few weeks and nothing seems to work except one solution that I found by mistake that probably shouldn't have worked.

Here's an example of how my data looks like:

Notice that Item2 is missing from the table in July 2021 and Item4 is missing from July 2022.

Here's the base formula I'm trying to achieve assuming my data has no missing prices:

Normally, what I would do in Excel to ignore missing prices is adding a condition where I multiply by 1 if both values are present and 0 if not. So in Dax I would add that condition in the SUMX such that the expression becomes Prices[Price]*[Sales]*[Condition]. However this method only works if I filter that condition based on a duplicated table, not if I use the original table. And it also only works if I create a separate measure, not if I create it as a variable inside the formula.

Here's my condition that works (notice that I use 'PricesDup' instead of the 'Prices' table in the formula above):

And here are the results using the base formula above and with the conditions added (one with the duplicated table the other one using the original). The correct result is the one in the middle. 

While this solution works so far, duplicating a table seems like a bad idea that is prone to break eventually. And I don't understand why using the original table in the condition returns NaN. I've tried so many different ideas, but this is the only one that somehow worked. 

Does anyone have any idea why duplicating the table and filtering on the duplicate works, while using the original doesn't? Is there a better solution that doesn't rely on a duplicated table? I'm open to any suggestions.

Note also that the fixed dates are placeholders to test my formula, I'm trying to achieve this dynamically so they will be replaced by a parameter in the final version.

Here's my sample file: https://drive.google.com/file/d/1TOfcbYETMhfdSDpPe7s6QkFOaca5XuSd/view?usp=sharing

Thanks in advance for all your efforts!

[giri41]

sumx(SUMMARIZE(ATC,ATC[top_level_account],ATC[customer_segment]),

SUMX(VALUES(ATC[material_style1]),

 if( [Price per Unit_CY]=0||[Price per Unit_PY]=0,[Sales Variance],[Quantity_variance]*[Price per Unit_PY]))

) 

please help.. this is ignoreing few rows in the table

[johnt75 · Accepted solution]

Try

YOY % increase =
VAR products2021 =
    CALCULATETABLE (
        VALUES ( 'Prices'[Item] ),
        'Prices'[Date] = DATE ( 2021, 7, 1 )
    )
VAR products2022 =
    CALCULATETABLE (
        VALUES ( 'Prices'[Item] ),
        'Prices'[Date] = DATE ( 2022, 7, 1 )
    )
VAR allProducts =
    INTERSECT ( products2021, products2022 )
VAR prices2021 =
    CALCULATE (
        SUM ( 'Prices'[Price] ),
        allProducts,
        'Prices'[Date] = DATE ( 2021, 7, 1 )
    )
VAR prices2022 =
    CALCULATE (
        SUM ( 'Prices'[Price] ),
        allProducts,
        'Prices'[Date] = DATE ( 2022, 7, 1 )
    )
RETURN
    DIVIDE ( prices2022, prices2021 ) - 1

[Anonymous]

Your solution worked, I just had to replace SUM(Prices[Price]) with SUMX(Prices,Prices[Price]*[Sales]) to have the prices weighted.

Thank you very much!