cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mcornfield
Helper I
Helper I

Calculate formula not Totaling

Can anyone help me with this formula?

 

Calculate formula not totaling 

 

mcornfield_0-1615572432965.png

 

7 REPLIES 7
jameszhang0805
Resolver IV
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 )
        )
AntrikshSharma
Resident Rockstar
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

This gave me a total but the the wrong subtotals

mcornfield_0-1615818356721.png

 

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

@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 ))

This solution did not total either for some reason

mcornfield_1-1615818422065.png

 

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]
)

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

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.