Matrix Visual Total Wrong

[RENJITH_R_S]

Hello Friends, Can you help me on this issue. I have 2 measures actuals and forecast and 2 columns cost and fiscal year. I want to visual a matrix with this data with a condition for fiscal year 2024 actuals needs to show and for others forecast. see the below screenshot . I have created new measure 

IF(SELECTEDVALUE('Calendar'[Fiscal Year])=2024,[Actuals],[forecast]) in the values it is working correct but in the total part calcualtion is not working. total picks the forecast value for 2024. please help on this

[RENJITH_R_S · Accepted solution]

I got the answer
Result = 

var a = CALCULATE([Actuals],FILTER('Calendar','Calendar'[Fiscal Year]=2024))

var b = CALCULATE([Forecsst],FILTER('Calendar','Calendar'[Fiscal Year]<>2024))

var c = a+b

return c

[GrowthNatives]

Hi @RENJITH_R_S , this issue you're facing is a classic DAX total vs. row context problem in Power BI. Your current measure using:

IF(SELECTEDVALUE('Calendar'[Fiscal Year]) = 2024, [Actuals], [Forecast])

works for individual fiscal years in the row context, but fails at the total level because SELECTEDVALUE('Calendar'[Fiscal Year]) returns blank when multiple years are in context (like in the Total row), so it defaults to [Forecast].
What we need:

• Show [Actuals] only for 2024
• Use [Forecast] for all other years
• Ensure the total reflects this mixed logic

✅ Fix: Use SUMX over Years

Instead of relying on SELECTEDVALUE which fails in totals, use SUMX to evaluate row-wise logic:

Final Cost = 
SUMX(
    VALUES('Calendar'[Fiscal Year]),
    IF(
        'Calendar'[Fiscal Year] = 2024,
        [Actuals],
        [Forecast]
    )
)

🔍 Explanation:

• VALUES('Calendar'[Fiscal Year]) gets a list of individual years (even in the Total row).

• SUMX(...) iterates over each year and applies your logic.

• It adds [Actuals] for 2024 and [Forecast] for other years.
  
  

✅ Now this will show correct values in the matrix cells & accurate total (1 + 2 + 1 + 1 + 1 = 6)

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! 🚀

[RENJITH_R_S]

@GrowthNatives - Thanks for the response. As per your formula, only actuals means fiscal year 2024 data is showing. no other years are showing