Power Pivot running total by categrory repeating for other groupings.

[dicken]

Hi,
I've had this problem before and am pretty certain use and "IF" statement to solve it but cannot get the right result,
Example formula ;

RTM Just  a :=
VAR calcA =
    CALCULATE (
        [totalSales],
        FILTER (
            CROSSJOIN ( ALL ( Table1[Date] ), ALL ( Table1[Item] ) ),
            Table1[Date] <= MAX ( Table1[Date] )
                && Table1[Item] = "a"
        )
    )
RETURN
   CalcA

This gives the correct result but repeats it for every grouping not just 'a' how can I stop this? There are Totals for every row so can't use ;
IF(TotalSales, Calc , Blank()) , but think I need to use something along these lines.

Richard.

 

[dicken]

Hi,
I've had this problem before and am pretty certain use and "IF" statement to solve it but cannot get the right result,
Example formula ;

RTM Just  a :=
VAR calcA =
    CALCULATE (
        [totalSales],
        FILTER (
            CROSSJOIN ( ALL ( Table1[Date] ), ALL ( Table1[Item] ) ),
            Table1[Date] <= MAX ( Table1[Date] )
                && Table1[Item] = "a"
        )
    )
RETURN
   CalcA

View attachment 74382

This gives the correct result but repeats it for every grouping not just 'a' how can I stop this? There are Totals for every row so can't use ;
IF(TotalSales, Calc , Blank()) , but think I need to use something along these lines.

Richard.

Use keepfilters in the total sales measure

Just 'a' Total Sales:=CALCULATE([Total Sales], KEEPFILTERS(FILTER(ALL(Table1[Item]),Table1[Item]= "a" ) ) )