Calculate formula not Totaling

[mcornfield]

Can anyone help me with this formula?

Calculate formula not totaling 

[jameszhang0805]

try this code :

Rev SWLY2 =
VAR _weekid52 =
    MAX ( DateTable[WeekID] ) - 52
RETURN
    SUMX (
        VALUES ( DateTable[WeekEnding] ),
        CALCULATE (
            SUM ( 'Fact Table'[Revenue Net] ),
            FILTER ( ALL ( DateTable ), DateTable[WeekID] = _weekid52 )
        )

[AntrikshSharma]

@mcornfield SELECTEDVALUE only return a single value and at the grand total there are no filters so you have to use MAX or MIN like this:

Rev =
VAR MaxDate =
    CALCULATE (
        MAX ( DateTable[WeekID] ),
        FILTER ( VALUES ( Dates[Calendar Year Number] ), [Revenue Net] <> 0 )
    )
VAR Result =
    CALCULATE (
        [Revenue Net],
        DateTable[WeekID] = MaxDate - 10,
        REMOVEFILTERS ( DateTable )
    )
RETURN
    Result

[mcornfield]

This gave me a total but the the wrong subtotals

[AntrikshSharma]

@mcornfield  hmmm actually, I made a guess based on @themistoklis  code, if none of the solutions on this page works then you have to share power bi file, also make sure going forward you provided data upfront so that it save everyone's time 🙂

[themistoklis]

@mcornfield 

try this formula

Rev SWLY2 = 
var _weekid52 = SELECTEDVALUE(DateTable[WeekID]) - 52
return
CALCULATE(SUM('Fact Table'[Revenue Net]), FILTER(ALL(DateTable), DateTable[WeekID] =_weekid52 ))

[mcornfield]

This solution did not total either for some reason

[Anonymous]

Hi @mcornfield , you need use SUMX and Summarize in another measure.

try this

MeasureTotal = SUMX (

ADDCOLUMNS (

SUMMARIZE ( 'Table', 'Table'[Date] ),

"Result", [First Measure without Total]

),

[Result]

)