Cumulative Total DAX

[llavey]

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])))

[Sean]

---

 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....

[Sean]

@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]

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

[Sean]

@llavey Not sure what you mean?

[llavey]

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.

[Sean]

As far as this new question...

What is Cumulative Exp Shimpments? It was not included in the sample data?

[llavey]

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.

 

[Sean]

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

[llavey]

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?

[v-sihou-msft]

@llavey

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,

[Neuro81]

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])))