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.
Hello,
I've extracted a simple GL Dataset out of my GL (have it set up so I can extract by the month to keep things simple). Within PowerQuery (Get & Transform) I've shaped my data so that it's pivoted by the date (y axis in pic) and GL Account (x axis in pic). Is it possible for me to now put a command into PowerQuery so that it will simply cumulatively total each of the GL columns top to bottom?
In the example attached, the first 5 columns would remain unchanged from 6/1-6/30 as no balances were added to them. Conversly, in the sixth column (headed by GL 21668), it would read 770000 through June 5th, to then it would read 0 to the bottom.
After this my plan is to then unpivot the columns and I will be left with a cumulative total by date & by GL code.
Any help would be much appreciated, thanks!
Hi,
I would suggest that you unpivot your dataseta and have the GL codes in a single column. So your dataset should be a 3 column one - Date. GL code and Amount. From this dataset build your visual.
Hi Ashish,
Agreed on the unpivot, but prior to doing that, I need to get each GL column cumulatively summed downwards. is this possible without too much sophistication?
Don't know. Sorry.
Not sure about the M but perhaps an easier way is to use create a Measure that calculated Cumulative totals and then use the Summarize in DAX to build a table using DAX (the New Table) button is on the Modeling TAB) that will build a table by Date and GL codes with the Cumulative totals. After the table is created you can link it into your model as you would any other table.
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |