Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
avachris
Frequent Visitor

Add Column Summarize Pattern not returning the correct result with calculated dimension

@tamerj1  - sorry about the earlier post, I was a bit frazzeled when I wrote it up. Hopefully this is more clear.

 

I am unable to obtain the correct result from an add columns summarize pattern, when I am filtering on a dimension which does not exist within the fact table. 

 

I am working with both forecasts and actuals, and I would like to create a version which is called "Forecast + Actuals" which is a combination of actuals and the forecast values which is after the last date of actuals. 

 

I know you can do this via a measure like if selected value = "Forecast + Actuals" then return calculate measure, but I would prefer to do it in one measure to reduce the complexity of my measure tree.

 

Here is an example data set. 

 

DateVersionValue
31-OctForecast100
30-SepForecast150
30-SepActual120
31-AugActual100

 

Here is the Version Dimension table. "Forecast + Actuals" doesn't exist in my fact table, but can be derived from the fact table through a combination of Forecast and Actuals. 

Version Dimension Table
Forecast
Actuals
Forecast + Actuals

 

I would like a measure which would populate the following: 

Version8/319/3010/31
Forecast 150100
Actuals100120 
Forecast + Actuals100120100

 

I was trying to accomplish it like this via this measure: 

DataSum = 

 VAR MaxActualsDate =
    CALCULATE ( MAX ( FactTable[Date], Version = "Actuals" ) )
VAR SummaryData =
    ADDCOLUMNS (
        SUMMARIZE ( 'FactTable', 'Date'[Date], Versions[Version] ),
        // this is the core table
        "@SourceValue", CALCULATE ( SUM ( FactTable[Value] ) )
    )
RETURN
    IF (
        SELECTEDVALUE ( Versions[Version] ) = "Current Forecast + Actuals",
        CALCULATE ( SUMX ( SummaryData, [@SourceValue] ), [Version] = "Actuals" )
            + CALCULATE (
                SUMX ( SummaryData, [@SourceValue] ),
                [Version] = "Forecast",
                Date[Date] > MaxActualsDate
            ),
         SUMX ( SummaryData, [@SourceValue] )
    )

 

I didn't find any success here - so then I decided to modify the variable table by unioning the "Forecast + Actuals" into the SummaryData variable table. When i look at this in DAX studio, the table does have  "Forecast + Actuals" with values, however, I am unable to bring this into a visual.

 

DataSum = 

var MaxActualsDate = calculate( max(FactTable[Date],Version="Actuals")

Var SummaryData = ADDCOLUMNS (
    TREATAS (
        UNION (
            SUMMARIZE ( 'FactTable', 'Date'[Date], Versions[Version] ), // this is the core table
            ADDCOLUMNS (
                SUMMARIZE ( FactTable', 'Date'[Date], Versions[Version]  ),
                "Version", "Current Forecast + Actuals" // this is where i'm trying to add the "fake data series"
            )
        ),
        'Date'[Date],
        Versions[Version]
    ),
    "@SourceValue",
        IF (
            [Version] = "Current Forecast + Actuals",
            CALCULATE ( SUM ( FactTable[Value] ), ALL ( Versions ), Versions[Version] = "Actuals" ),
            CALCULATE ( SUM (FactTable[Value])
        )
)

 

Return Sumx ( SummaryData, [@SourceValue])

 

 

 

5 REPLIES 5
tamerj1
Super User
Super User

Hi @avachris 

can provide sample of data?

Its something like this - 

 

DateVersionCurrencyRWA_AIRB
10/31BCL DataUSD100
10/31BCL DataCAD100
10/31UATCAD100

@avachris 

It is still not very clear. Can we connect via teams or zoom. I'll be free after 30 min

@Member123456  this isn't spam.

Yeah you are right let me rework the question and post a picture of the data model

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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