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.⚡
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.
edhans you this line in the signature is saying everything DAX is for Analysis. Power Query is for Data Modeling
Regardless, it is sum or avg or count, I don't see any value of doing the aggregation in power query specific to this particular post.
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