User Profile
jk8979356
Regular Visitor
Joined 5 years ago
User Widgets
Contributions
How to sum multiple rows from another table
I have a table that has [Subsidiary] [Category] [2019 Sales] [2020 Sales] columns with thousands of rows and I need to either sum the 2019 & 2029 sales rows in this table or in a new reference table so that the output is [Subsidiary] [Category][2019 Sales][2020 Sales] {Sub1}{Cat1}{19 sales total}{20 sales total} {Sub1}{Cat2}{19 sales total}{20 sales total} {Sub2}{Cat1}{19 sales total}{20 sales total} {Sub2}{Cat2}{19 sales total}{20 sales total} Where each row sales total is the total for only that specific subsidiary>category combo. What is the cost to do this?How should I design relationships between 3 tables?
Forgive me as I don't fully understand the proper way to design table relationships. I have three tables of transaction data: 2019 Actual Sales, 2020 Sales Forecast, 2021 Sales Forecast. Each table has the same 31 data columns, except the 2020 Sales Forecast table which has an additional 2 calculated columns. These are tables full of transactional data with no unique identifier keys. I am trying to use Power Query / Power Pivot to combine these 3 tables into a single pivot with multiple data rows that each use the Quantity & Revenue columns for the pivot table "Values". How do I create the relationships so I can compare Values from the 3 tables for the common columns shown here. Keeping in mind each table has thousands of different entries for each data row type.How do I nest an IF statement rather than create a new column for it?
I am using Power Query to take year to date sales data and compare it to our estimated total sales for the entire year. With this YTD vs full year comparison I am creating a "Forecast Factor" number that I then use to estimate the total item level sales for the full year. For example: Year to date Sales: $900 Estimated total sales for year: $1000 Forecast factor: 900 / 1000 = 90% Widget ABC YTD Sales: $10 Forecasted Widget ABC sales for full year: 10 / .90 = $11.11 Now where this formula breaks down is if my year to date sales exceeds my estimated total sales for year: Year to date Sales: $1100 Estimated total sales for year: $1000 Forecast factor: 1100 / 1000 = 110% Widget ABC YTD Sales: $10 Forecasted Widget ABC sales for full year: 10 / 1.1 = $9.09 So to account for this, I created an if statement that says if YTD sales are <= estimated total sales, then use (YTD sales / total estimated sales) to create a "Forecast Factor" that the Widget sales is then divided by (example 1 above). Else, return a value of 1 for the Forecast Factor. = Table.AddColumn(#"Added Sales Forecast Column", "Forecast Factor", each if List.Sum(#"Changed Type2"[Amount]) <= #"2020forecast"{0}[Column2] then (List.Sum(#"Changed Type2"[Amount]) / #"2020forecast"{0}[Column2]) else 1) I am currently putting this "Forecast Factor" in as a new column. My question is there anyway to skip adding the forecast factor as a new column and instead nest the IF statement directly into my Widget ABC forecasted sales column? Currently my Widget forecast code is. How would I change this code, to nest the Forecast Factor IF statement directly into it? = Table.AddColumn(#"Added Qty Forecast Column", "Sales Forecast", each if [Amount] <= 0 then [Amount] else [Amount] / [Forecast Factor])
