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.⚡
Super UserAnonymous , 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
- edhans
Community Champion
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.
- parry2k
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.
- edhans
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!
