Forum Discussion
Pivot on Multiple Dimensions and Values
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.⚡
Thanks parry2k - I guess I am not clear then. Say I have this data:
| Date | Invoice | Product | Amount |
| 7/10/2020 | 123 | A | 88 |
| 7/10/2020 | 123 | C | 29 |
| 7/10/2020 | 123 | J | 37 |
| 7/10/2020 | 455 | E | 78 |
| 7/10/2020 | 456 | K | 94 |
| 7/10/2020 | 456 | A | 35 |
| 7/10/2020 | 456 | F | 84 |
| 7/10/2020 | 678 | G | 21 |
| 7/10/2020 | 678 | I | 42 |
| 7/10/2020 | 678 | F | 61 |
| 7/10/2020 | 999 | G | 86 |
| 7/10/2020 | 999 | D | 56 |
| 7/10/2020 | 999 | B | 85 |
| 7/10/2020 | 999 | D | 98 |
| 7/10/2020 | 999 | D | 23 |
| 7/10/2020 | 999 | I | 27 |
If I want total sales by product, that is easy, as you said.
If want to the average daily sales for each product though, wouldn't I need to first sum the products by day, then do an average? Whereas if the data granularity was at the daily level and the invoices were removed, both the sum and average would be equally easy.
Also, if I wanted to get a count of how many days "D" was sold, I would have to wrap the count in DISTINCT whereas if the invoices were gone, I could just use COUNT, or COUNTX.
The reason I have Power Query is for modeling and DAX is for analysis, I use them equally, and I use Power Query to get my data in the granularity I need, and, like Anonymous I almost never care about invoice level detail, so I do similar modeling to summarize by daily, or even monthly, activity, before bringing it in to DAX.
But where am I missing what you are saying here? FWIW, I am working my way through SQLBI's DAX Modeling course and having data at the right granularity to support the analysis you want to provide seems to be a key point in many of their lectures, but I am still learning, so what would be the advantage of having more detailed granularity in the data than is needed by the analsysis just because it comes from the source system at that more detailed level?
Thanks!
edhans Bringing the data to the same granularity is one thing but aggregation is another. If data is already at the granular level along with other facts in the model, I don't see the reason to aggregate in power query, how about if someone wnt to drill down to give the list of the invoices. Again, this all depends on what business question someone is trying to answer (immediate need) and what could potential questions in the future. Once data is aggregated and if there is a need in the future to get more granularity, means you have to go back to your transformation and that can be very very challenging and pretty much going back to square one.
Again, it is not that doing grouping in Power Query is wrong but I will not do this and so far worked with many small/mid/large datasets, maybe only once or twice I have to do grouping Power Query coz of granularity. Keep in mind, the grouping is a very expensive query and on a large dataset, it can slow down the whole loading of the data. Too many factors.
Cheers,
P