Excel 365 Pro Plus with Power Pivot, Power Query and DAX Studio.
Works in this setup.
Share file.
https://www.mediafire.com/file/tl6omktwah8vk9u/11_10_21.xlsx/file
https://www.mediafire.com/file/x26g5rurjyv9q3z/11_10_21.pdf/file
Sum variables based on 2 conditions from multiple tables using Excel Powerpivot data model
Problem input: I have 3 columns:
- Column 1: Week start date (variable)
- Column 2: SKU (variable)
- Column 3: SPA Sales (variable)
Question: how do I sum up “SPA Sales” for every “SKU” in a specific “Week start date” to get a total of “SPA Sales” for every “Product” in every “Week Start Date”. Note: values in "SKU" contain duplicate values for the same "Week start date"
Expected outcome description:
In the attached file - column "Raw2-SPA", you will need to sum up the values from the column "SPA Sales" for every SKU in every "Week Start" date range so the output will be:
Week Start: X1
SKU: Y1
Sales: Z1
Week Start: X2
SKU: Y1
Sales: Z2
and so on for all the SKUs and all the Weeks
Attempted Solution: =SUMMARIZE(BR,'BR'[Week Start],'BR'[SKU],"Total Sales Test", SUM(BR[Total Sales])))
Error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
From what I understood, the output should be a calculated table because I need totals based on 3 varying conditions/criteria, but I cannot implement it in Powerpivot DAX.
1 answer
Sort by: Most helpful
-
Herbert Seidenberg 1,191 Reputation points
2021-11-10T18:47:39.88+00:00