How to sum weekly total and 13-week avergage in total column using same measure.

[cheid]

I have matrix where I want to sum the weekly totals and put the 13 week average in the total column?  I am using the below to calculate the 13 week average and the other measure to combine the weekly sum with the 13 week average, but the numbers are much higher than they should be.  The final result I want is in the last screenshot.  Any help you can provide is appreciated.

Fixed combo = IF(

    ISINSCOPE('LookUp Calendar'[Date]),

    sum('Fixed'[Charge]),

    [Total Fixed - 13 Wks])

Total Fixed - 13 Wks =

VAR _Total =

CALCULATE(

    SUM('Fixed'[Charge]),

    DATESINPERIOD('LookUp Calendar'[Date],

    MAX('LookUp Calendar'[Date]), -91,DAY)

)

RETURN

_Total

[grazitti_sapna · Accepted solution]

Hi @cheid,

This issue is due to how calues are calculated in Power BI, Totals do not aggregate the visible values; instead they recalculate the measure in a different filter context. Because of that, your 13-week window is being evaluated multiple times, which inflates the result.

The Measure which you've created returns sum of 13 weeks and not the average, and when used in totals it evaluates over the entire matrix context, which causes the large numbers.

You should use INSCOPE function to get the desired result.

I've attached a solution .pbix file with an example.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.