Row LabelsSum of Ext QtyCumulative Extr Qty FailsSum of DELIVERY_QTY_EXPExtr DPMCumulative Extr DPM2015-11224223.7605022222,282,7871.01.02015-12315539.1262961174,694,9471.81.42016-01262801.1768099177,758,6551.51.42016-02103903.8266581211,934,8930.51.12016-033906.8266581197,277,2940.00.92016-045911.3928682197,876,4620.00.82016-0500158,747,5080.00.0Grand Total911911.39286821,340,572,5460.70.7When the Sum of Ext Qty = 0, then i want the Cumulative Extr Qty Fails to add that 0 and always show a Cumulative total. But it doesn't, it just shows 0. How do i modify my formula to always show running total, even if Sum of Ext Qty = 0? My DAX formula:Cumulative Extr Qty Fails:=calculate(if(sum('A'[Extrapolated Qty])=BLANK(),0,sum ('A'[Extrapolated Qty])),FILTER(ALLSELECTED ('A'),A[Year-Month]<=MAX ('A'[Year-Month])))

llavey Is this a trick question? Get rid of the condition...Cumulative Extr Qty Fails := CALCULATE ( SUM ( 'A'[Extrapolated Qty] ), FILTER ( ALLSELECTED ( 'A' ), A[Year-Month] <= MAX ( 'A'[Year-Month] ) ) )

Why would i get rid of this? I need it for the Cumulative DPM for each month?

So, you've added another Cumulative Ext Qty Fails column to the pivot and made it a running total, which works great. The problem is I have a Cumulative Ext DPM column which will take the Cumulative Ext Qty Fails/Cumulative Exp Shipments*1000000 for each month. Any idea how to accomodate that if I get rid it? Right now, i have another DAX formula for the Cumulative DPM that uses the Cumulative Extr Qty Fails:Cumulative Extr DPM:=([Cumulative Extr Qty Fails]/[Cumulative Exp Shipments])*1000000 Maybe i'm making this way harder than it should be.

Forum Discussion

llavey

Regular Visitor

10 years ago

Cumulative Total DAX

Row Labels	Sum of Ext Qty	Cumulative Extr Qty Fails	Sum of DELIVERY_QTY_EXP	Extr DPM	Cumulative Extr DPM
2015-11	224	223.7605022	222,282,787	1.0	1.0
2015-12	315	539.1262961	174,694,947	1.8	1.4
2016-01	262	801.1768099	177,758,655	1.5	1.4
2016-02	103	903.8266581	211,934,893	0.5	1.1
2016-03	3	906.8266581	197,277,294	0.0	0.9
2016-04	5	911.3928682	197,876,462	0.0	0.8
2016-05	0	0	158,747,508	0.0	0.0
Grand Total	911	911.3928682	1,340,572,546	0.7	0.7

When the Sum of Ext Qty = 0, then i want the Cumulative Extr Qty Fails to add that 0 and always show a Cumulative total. But it doesn't, it just shows 0. How do i modify my formula to always show running total, even if Sum of Ext Qty = 0?

My DAX formula:

Cumulative Extr Qty Fails:=calculate(if(sum('A'[Extrapolated Qty])=BLANK(),0,sum ('A'[Extrapolated Qty])),FILTER(ALLSELECTED ('A'),A[Year-Month]<=MAX ('A'[Year-Month])))

Need Help

11 Replies

Sean
Community Champion
10 years ago
llavey Is this a trick question?

Get rid of the condition...
Cumulative Extr Qty Fails := CALCULATE ( SUM ( 'A'[Extrapolated Qty] ), FILTER ( ALLSELECTED ( 'A' ), A[Year-Month] <= MAX ( 'A'[Year-Month] ) ) )
- llavey
  Regular Visitor
  10 years ago
  Why would i get rid of this? I need it for the Cumulative DPM for each month?
  - Sean
    Community Champion
    10 years ago
    llavey Not sure what you mean?
Sean
Community Champion
10 years ago
How do i modify my formula to always show running total, even if Sum of Ext Qty = 0?

My DAX formula:
Cumulative Extr Qty Fails:=calculate(if(sum('A'[Extrapolated Qty])=BLANK(),0,sum ('A'[Extrapolated Qty])),FILTER(ALLSELECTED ('A'),A[Year-Month]<=MAX ('A'[Year-Month])))
llavey I believe i answered your original Question...

And no I did not create any new columns.
All I did was change the MEASURE formula....

Forum Discussion

Cumulative Total DAX

11 Replies

Related content

Cumulative Total

Cumulative Total

Cumulative (Running) Totals in DAX

DAX for Cumulative Total

Cumulative Count DAX