cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper III

## 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 )
)``````
Community Champion

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

This gave me a total but the the wrong subtotals

Community Champion

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

Community Champion

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 III

This solution did not total either for some reason

Anonymous
Not applicable

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors