question

AlexandrSemichin-0525 avatar image
0 Votes"
AlexandrSemichin-0525 asked HerbertSeidenberg-6295 answered

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.

power-query-not-supportedoffice-scripts-excel-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

HerbertSeidenberg-6295 avatar image
0 Votes"
HerbertSeidenberg-6295 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.