Forum Discussion
Cumulative Total DAX
All I really did was just modify the Measure which sums the values in [Sum of Ext Qty] column - nothing else
That zero is from the sample data you posted - I included it in the Matrix just to compare - I have not created any columns
The original table i posted was a copy of the pivot table in Excel Services. The raw data in Visual Studio looks like this:
| Lot ID | CURRENT_QTY | Extrapolated Qty |
| CQMHY82.11 | 1 | 16 |
| CQG7Y82.11 | 1 | 16 |
| CQ43172.11 | 1 | 16 |
| CQF3172.11 | 1 | 16 |
| CQN4172.11 | 1 | 16 |
My DAX formulas are:
Sum of CURRENT_QTY:=if(SUM('DRAMRMACompData_append60saboveB'[CURRENT_QTY])=BLANK(),0,SUM(DRAMRMACompData_append60saboveB[CURRENT_QTY]))
Sum of Ext Qty:=if(sum([Extrapolated Qty])=BLANK(),0,sum([Extrapolated Qty]))
**bleep** Extr Qty Fails:=calculate(sum(DRAMRMACompData_append60saboveB[Extrapolated Qty]),FILTER(ALLSELECTED('DRAMRMACompData_append60saboveB'),'DRAMRMACompData_append60saboveB'[Year-Month]<=MAX('DRAMRMACompData_append60saboveB'[Year-Month])))
My powerpivot table looks like when i drag my measures over to it. See the blanks in Cumulative Extr Qty Fails where the value for Current Qty is 0? If there is a 0 in Current Qty, i want the Cumulative Extr Qty Fails to just add 0 and continue as a running total with no blanks.
| Year-Month | Sum of CURRENT_QTY | Sum of Ext Qty | Cumulative Extr Qty Fails |
| 2014-11 | 0 | 0 | |
| 2014-12 | 0 | 0 | |
| 2015-01 | 0 | 0 | |
| 2015-02 | 0 | 0 | |
| 2015-03 | 0 | 0 | |
| 2015-04 | 1 | 5 | 4.566210046 |
| 2015-05 | 2 | 9 | 13.69863014 |
| 2015-06 | 0 | 0 | |
| 2015-07 | 1 | 5 | 18.26484018 |
| 2015-08 | 0 | 0 | |
| 2015-09 | 4 | 11 | 29.39726027 |
| 2015-10 | 6 | 27 | 56.79452055 |
| 2015-11 | 0 | 0 | |
| 2015-12 | 4 | 18 | 75.05936073 |
| 2016-01 | 34 | 155 | 230.3105023 |
| 2016-02 | 6 | 27 | 257.7077626 |
| 2016-03 | 0 | 0 | |
| 2016-04 | 1 | 5 | 262.2739726 |
| 2016-05 | 0 | 0 | |
| Grand Total | 59 | 262 | 262.2739726 |
Any idea what's wrong with my formulas?
Hi llavey,
I cannot reproduce your problem. Both measures provided by Sean and you are working properly with your sample data. My Power BI Version is 2.35.4399.381 64-bit (May 2016). “Cumulative Extr Qty Fails” can always show a cumulative total even when the Sum of Ext Qty = 0.
A_Cumulative Extr Qty Fails =
CALCULATE (
SUM ( 'A'[Sum of Ext Qty] ),
FILTER ( ALLSELECTED ( 'A' ), A[Year-Month] <= MAX ( 'A'[Year-Month] ) )
)
B_Cumulative Extr Qty Fails =
CALCULATE (
SUM ( B[Sum of Ext Qty] ),
FILTER (
ALLSELECTED ( B ),
B[Year-Month]
<= MAX ( B[Year-Month] )
)
)
Are you working in Power BI Desktop? Please install the latest update Power BI Desktop.
If you still can’t make it work, please share the .pbix file if possible.
Regards,
