cancel
Showing results for
Did you mean:  Helper I

## Calculate formula not Totaling

Can anyone help me with this formula?

Calculate formula not totaling 7 REPLIES 7  Resolver IV

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 )
)``````  Resident Rockstar

@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
``````  Helper I

This gave me a total but the the wrong subtotals   Resident Rockstar

@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 🙂  Super User II

try this formula

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

This solution did not total either for some reason   Helper I

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

try this

MeasureTotal = SUMX (
SUMMARIZE ( 'Table', 'Table'[Date] ),
"Result", [First Measure without Total]
),
[Result]
) Announcements #### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022. #### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories. #### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals. Top Solution Authors
Top Kudoed Authors
Users online (2,726)