Forum Discussion
Pivot on Multiple Dimensions and Values
I have a sales table with the following columns:
* Date
* Product ID
* Sales Value
* Profit
Because this comes off an invoicing table, there are multiple entries for each date/product ID combination. I want to combine these, so that each date and product has only one line, with the sales value and profit summed for each. How do I do this in the Power Query Editor?
Anonymous first you don't need to sum these in power query, you can load raw data and just sum in your visual. If you still want to do grouping in power query, there is group by option in the menu, pick , date to group by and use two columns to sum in the same dialog box.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
10 Replies
Hi Anonymous,
Providing a sample of your data would help. Also, you seem to have missing some columns in your fact table. How do you know to what invoice number does each record correspond?
F
Anonymous first you don't need to sum these in power query, you can load raw data and just sum in your visual. If you still want to do grouping in power query, there is group by option in the menu, pick , date to group by and use two columns to sum in the same dialog box.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
In Power Query:
- Select the Date and Product ID columns
- On the home ribbon, select Group By
- In the bottom section add the Sales value, call it Sales, Sum operation, and Sales column
- Add an aggregation, then do Profit, Sum Operation, Profit column.
It would look like this, but with your columns.
It is good you are doing this. Having data at the right granularity is key to a good data model, and if you don't need the duplications of dates and product IDs, Power Query is the best way to do this.
Anonymous , Why do you want that in power query. Save this and use this visualization tab. You just need to take a sum of profit and sales. (Default for numbers is sum)
Use table, Matrix or any other visual. That should be done.
refer this video - https://www.youtube.com/watch?v=m1eLTtZHGs4&feature=youtu.be
amitchandak what if they want to also include a count, or a daily average? Won't that make the DAX more complex? I know doing a SUM really makes no difference.
I would have thought other aggregations would be more difficult if the data was at a different granularity.
