llavey Tracker

Re: Cumulative Total DAX

llavey — Mon, 23 May 2016 15:15:58 GMT

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?

Re: Cumulative Total DAX

llavey — Fri, 20 May 2016 20:22:01 GMT

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.

Re: Cumulative Total DAX

llavey — Fri, 20 May 2016 18:35:39 GMT

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.

Re: Cumulative Total DAX

llavey — Fri, 20 May 2016 18:15:28 GMT

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

Cumulative Total DAX

llavey — Fri, 20 May 2016 17:24:20 GMT

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