cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Column Subtotal Issue

I have a measure that pulls Actuals data from Jan-Current Month and Forecasted data for the rest of the year with a formula:

CALCULATE(IF(
Max(Master_Date[Daily]) <= Today(),
Sheet1[Actuals],
Sheet1[FCST]))

The issue is that when I filter on the full year, it only shows Forecast totals, but if I filter down to August (The last month of Actuals) then it'll show me the correct Subtotal for Jan-Aug. I need to figure out how to make it show me Jan-Dec subtotals where it sums Jan-Aug Actuals and then Sep-Dec Actuals.
1 ACCEPTED SOLUTION
Community Champion

The problem here is that when you filter to the full year, the context of your measure doesn't know how to differentiate between months that should use `Actuals` and those that should use `Forecasted` data.

You need to iterate through each day in your current context and determine whether to take `Actuals` or `Forecasted` data for that day.

One approach is to use the `SUMX` function.

``````Total Actuals and Forecast =
SUMX (
VALUES ( Master_Date[Daily] ),
IF (
Master_Date[Daily] <= TODAY(),
CALCULATE ( SUM ( Sheet1[Actuals] ) ),
CALCULATE ( SUM ( Sheet1[FCST] ) )
)
)
``````

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696
2 REPLIES 2
Super User

@plerner16 I agree with @AmiraBedh, adding some content here that may help you understand what is going on. First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Community Champion

The problem here is that when you filter to the full year, the context of your measure doesn't know how to differentiate between months that should use `Actuals` and those that should use `Forecasted` data.

You need to iterate through each day in your current context and determine whether to take `Actuals` or `Forecasted` data for that day.

One approach is to use the `SUMX` function.

``````Total Actuals and Forecast =
SUMX (
VALUES ( Master_Date[Daily] ),
IF (
Master_Date[Daily] <= TODAY(),
CALCULATE ( SUM ( Sheet1[Actuals] ) ),
CALCULATE ( SUM ( Sheet1[FCST] ) )
)
)
``````

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors