cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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

11 REPLIES 11
Community Champion

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

And no I did not create any new columns.

All I did was change the MEASURE formula....

Community Champion

@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] ) )
)```
Regular Visitor

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

Community Champion

@llavey Not sure what you mean?

Regular Visitor

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.

Community Champion

As far as this new question...

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

Regular Visitor

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.

Community Champion

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

Regular Visitor

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?

Microsoft Employee

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

Helper I

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

-- ------------------------------ --
-- ------------------------------ --

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.