DAX studio and running total

[dicken]

Hi,
I have a calculated column , power pivot , returning a running total , (dex = index)

EVALUATE
VAR CurrentRow = SaleT[dex]
VAR CurrentItem = SaleT[item]
VAR filteredtable =
   FILTER ( ALL ( SaleT ), SaleT[dex] <= CurrentRow && SaleT[item] = CurrentItem )
RETURN
   CALCULATE ( SUM ( SaleT[Sales] ), filteredtable )

Which does return a running total calculated column, but but if i use addcolumns and the same in dax studio,

ADDCOLUMNS(SaleT, 
"new" , 
   CALCULATE ( SUM ( SaleT[Sales] ), filteredtable ) )

I just get the total reputed for each line, why? what am I doing wrong or not understanding.

Richard.

 

[Matt Allington]

Your first example has everything, including the variables, inside the row context of a calc column. You don’t show the entire code for the second, but I don’t see the variables. My assumption therefore is that the variables are outside of the code shown. Addcolumns has a row context too, and the variables need to be assigned while iterating the row context.

Get ride of the variables and hard code the filter function where the filtertable function is located now.

I can’t be sure without testing, but you may need another calculate wrapped around the existing calculate.

 

[dicken]

Your first example has everything, including the variables, inside the row context of a calc column. You don’t show the entire code for the second, but I don’t see the variables. My assumption therefore is that the variables are outside of the code shown. Addcolumns has a row context too, and the variables need to be assigned while iterating the row context.

Get ride of the variables and hard code the filter function where the filtertable function is located now.

I can’t be sure without testing, but you may need another calculate wrapped around the existing calculate.

Thanks for the explanation, I haven't tried it yet but I see what you mean,
I'm never quite sure about variables when I have multiple , i.e. variables inside what is another variable, I can usually get things working but it's a lot of trial and error.
Richard

 

[dicken]

Your first example has everything, including the variables, inside the row context of a calc column. You don’t show the entire code for the second, but I don’t see the variables. My assumption therefore is that the variables are outside of the code shown. Addcolumns has a row context too, and the variables need to be assigned while iterating the row context.

Get ride of the variables and hard code the filter function where the filtertable function is located now.

I can’t be sure without testing, but you may need another calculate wrapped around the existing calculate.

Sorry Matt,
Still not got a result, I hard coded the result which is what I would have done but found declaring filtered table as a var an interesting approach, espicially as up to now for column I've used EARLIER, I even have the code for using earlier in a measure stashed away , but its quite long and complex. I've taken a screen shot of the studio output.
in a column in pp I have

VAR dex = SaleT[dex]
            VAR aitem = SaleT[item]
                          RETURN
                                  CALCULATE (
                                                     SUM ( SaleT[Sales] ),
                                                    FILTER ( ALL ( SaleT ), SaleT[dex] <= dex && SaleT[item] = aitem )
                                                            )

Which gives me a running total column by index and item.

But this is what I've put into studio, which I thought would give me the same;

But as you can see this does not work, Shall persevere, I found some examples sqlbi, where the variable came directedly after the ADDCOLUMNS, but will have to disect it very slowly and then I still not understand it

Richard.

Richard.