Forum Discussion
Cumulative Total DAX
As far as this new question...
What is Cumulative Exp Shimpments? It was not included in the sample data?
Ah, i see what you did. However, I want the value for 2016-05 Cumulative Extr Qty Fails (highlighted in yellow) to be 911.392 and not 0.
This is my only question. Sorry for confusion.
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
if you have a calendar with a relationship you could try this
Cumulative Extr Qty Fails:=calculate(
sum ('A'[Sum of Ext Qty])
,FILTER(all('Calendar'[Date]),
'Calendar'[Date]<=MAX('Calendar'[Date])))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,
