Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
107 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |