Forum Discussion

nhanser's avatar
nhanser
Regular Visitor
10 years ago
Solved

DAX Running total by another column

Hello,   I need help to create a DAX column/measure that will create a running total by another column.   So for example, looking at this table Item | Date | QTY ------------------...
Need Help
  • LarsSchreiber's avatar
    LarsSchreiber
    10 years ago

    Hello nhanser,

     

    for me it looks like you want to add a calculated column, but the DAX code you use is for beeing used in a pivot table. 

     

    I copied your raw data, but used different dates.

     

    raw data

     

    The pivot I created looks like this and I hope this is what you want:

     

    Pivot

     

    The code I used is close to the one you used, but slightly different:

     

    RunningTotal :=
    CALCULATE (
     SUM ( Tabelle1[QTY] );
     FILTER ( ALL ( Tabelle1 ); Tabelle1[Date] <= MAX ( Tabelle1[Date] ) );
     VALUES ( Tabelle1[Item] )
    )

     

    The key here is the VALUES() which puts the Items into the filter context of the CALCULATE()-statement. Otherwise you would have running totals on the dates, but it would be the same for all your Items. 

     

    Hope that helps a bit.

     

    Greets,

    Lars

LarsSchreiber's avatar
LarsSchreiber
Icon for Responsive Resident rankResponsive Resident
10 years ago

Hello nhanser,

 

for me it looks like you want to add a calculated column, but the DAX code you use is for beeing used in a pivot table. 

 

I copied your raw data, but used different dates.

 

raw data

 

The pivot I created looks like this and I hope this is what you want:

 

Pivot

 

The code I used is close to the one you used, but slightly different:

 

RunningTotal :=
CALCULATE (
 SUM ( Tabelle1[QTY] );
 FILTER ( ALL ( Tabelle1 ); Tabelle1[Date] <= MAX ( Tabelle1[Date] ) );
 VALUES ( Tabelle1[Item] )
)

 

The key here is the VALUES() which puts the Items into the filter context of the CALCULATE()-statement. Otherwise you would have running totals on the dates, but it would be the same for all your Items. 

 

Hope that helps a bit.

 

Greets,

Lars

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Hi LarsSchreiber,

your solution didn't workout in my scenario..!!

 

DimItem(ItemKey,ItemNumber,CompanyKey) (One ItemNumber can have Multiple ItemKey because of CompanyKey)

FactRevenue(ItemKey,CompanyKey,CustomerKey,BrandKey,AccountingDate,Revenue) (Fact is day level)

Fact and Dim Item Joined on ItemKey.

when i try to put ItemNumber from DimItem and Revenue from FactRevenue in a table visual with following Measures.

 

TotalRevenue = CALCULATE(SUM(FactRevenue[Revenue]),ALL(DimItem[ItemNumber]))

RContr =  DIVIDE(SUM(FactRevenue[Revenue]),[TotalRevenue],0)

RT = CALCULATE([RContr],FILTER(ALL(FactRevenue),FactRevenue[AccountingDate] <= MAX(FactRevenue[AccountingDate])),VALUES(DimItem[ItemNumber]))

 

I am not getting good results. Please consider the output:

5.99+5.04 = 11.03 or something.