Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I receive a monthly report that shows YTD totals for a few different categories. I'm trying to find a solution within Power Query to convert these YTD totals into monthly totals. Any ideas?
Raw Data: Monthly Reports
JANUARY REPORT
Category A | 50 |
Category B | 100 |
Category X | 0 |
FEBRUARY REPORT
Category A | 125 |
Category B | 197 |
Category X | 15 |
MARCH REPORT
Category A | 185 |
Category B | 251 |
Category X | 39 |
Current Dataset:
MONTH | CATEGORY | YTD TOTAL |
January | Category A | 50 |
January | Category B | 100 |
January | Category X | 0 |
February | Category A | 125 |
February | Category B | 197 |
February | Category X | 15 |
March | Category A | 185 |
March | Category B | 251 |
March | Category X | 39 |
Target Dataset:
MONTH | CATEGORY | MONTHLY TOTAL |
January | Category A | 50 |
January | Category B | 100 |
January | Category X | 0 |
February | Category A | 75 |
February | Category B | 97 |
February | Category X | 15 |
March | Category A | 60 |
March | Category B | 54 |
March | Category X | 24 |
Solved! Go to Solution.
@stephanie116 see if this works. Here is what I did - as requested - in Power Query
End result:
You can see my PBIX file here if you want to play with it.
EDIT: I improved it a bit by only nesting the necessary data and doing the total calculation (List.Sum()) in a single setp. The step immediately after adding the index is now:
= Table.AddColumn(#"Added Index", "Cumulative YTD Total",
each let varCategory = [Category], varIndex = [Index]
in
List.Sum(
Table.SelectRows(#"Added Index", each [Category] = varCategory and [Index] <= varIndex)[Total]
), Int64.Type)
For a smaller data set (under 100,000 records probably, maybe more) this should work fine. I strongly encourage you to read @ImkeF's links and study them. I plan to dig into those later today and see if I can improve this even more for my own use going forward.
The original longer query is still in the PBIX linked to called [Table (Original Long Way)] to follow along, but [Table] consolidates a number of steps as shown above and pulls in only the necessary data for each record vs all the data for every record and then filtering in subsequent steps.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@stephanie116 see if this works. Here is what I did - as requested - in Power Query
End result:
You can see my PBIX file here if you want to play with it.
EDIT: I improved it a bit by only nesting the necessary data and doing the total calculation (List.Sum()) in a single setp. The step immediately after adding the index is now:
= Table.AddColumn(#"Added Index", "Cumulative YTD Total",
each let varCategory = [Category], varIndex = [Index]
in
List.Sum(
Table.SelectRows(#"Added Index", each [Category] = varCategory and [Index] <= varIndex)[Total]
), Int64.Type)
For a smaller data set (under 100,000 records probably, maybe more) this should work fine. I strongly encourage you to read @ImkeF's links and study them. I plan to dig into those later today and see if I can improve this even more for my own use going forward.
The original longer query is still in the PBIX linked to called [Table (Original Long Way)] to follow along, but [Table] consolidates a number of steps as shown above and pulls in only the necessary data for each record vs all the data for every record and then filtering in subsequent steps.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi there,
I come across a question like this before and made a blogpost about the solution: https://www.thebiccountant.com/2018/08/31/unravel-cumulative-totals-to-their-initial-elements-in-pow...
@edhans solution has a very nice and transparent logical path, but the performance could be improved if the logic would be applied on category-level, like I've described here:
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
You need to assign month a number like 1,2, 3 4 or month year 202001, 2020002, etc. Assume that column is Month No. Then try a new column
MONTHLY TOTAL = table[YTD TOTAL] -maxx(filter(Table,table[Month No] <= earlier(table[Month No]) && table[CATEGORY] = earlier(table[CATEGORY])),table[YTD TOTAL])
I don't know about Power Query, but I did write a solution in DAX:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Revenue-Reverse-YTD/m-p/373185#M111
@ImkeF might know how to solve it in M.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |