How do I keep a column in the filter context and perform a sumx calculation?

[Anonymous]

I'm trying to return the sumproduct of the next 4 years of a measure by a dynamic multiplier for different financial forecasts but I can't seem to get it to work. Wondered if anyone could help?

My data is in a flat table:

ForecastName    FiscalYear  TransactionType Value
Forecast1      2019         borrowing       10
Forecast1      2020         borrowing       10
Forecast1      2021         borrowing       10
Forecast1      2022         borrowing       20
Forecast1      2023         borrowing       25
Forecast2      2019         borrowing       30
Forecast2      2019         turnover        40
Forecast3      2019         turnover        50
Forecast3      2020         turnover        50

I have a measure to calculate borrowing:

Borrowing = CALCULATE(SUM(Table[Value]),Table[TransactionType]="borrowing")

And a measure for the mutiplier (basically it returns {1, 1, 0.5, 0}):

Multiplier = 
VAR YearNumber = MAX(Table[Fiscal Year])-2018
RETURN
IF(2.5 > YearNumber,1, IF(2.5 > YearNumber-1,.5,0))

Here's my attempt to write the sum product style measure:

Borrowing Forecast = 
VAR CurrentFiscalYear = MAX(Table[Fiscal Year])

RETURN
SUMX((FILTER(ALLEXCEPT(Table,Table[ForecastName]),
           Table[Fiscal Year] < CurrentFiscalYear + 4 &&
           Table[Fiscal Year] >= CurrentFiscalYear)
Mutiplier x Borrowing)

The idea being that when you put the measure on a table with FiscalYear on the row, filtered by ForecastName, it grabs the Fiscal Year from the row and puts it in the variable, then uses this to filter the whole table on the current and next 3 Fiscal Years but keeps the filter on ForecastName, then performs the calculation and sums it. The table shows my desired result for Forecast1:

Fiscal Year Borrowing Forecast - DESIRED
2019         25 ((10*1)+(10*1)+(10*0.5)+(20*0))
2020         30 ((10*1)+(10*1)+(20*0.5)+(25*0))

The actual result is a big number I don't understand but I think one of the problems is the ALLEXCEPT part doesn't seem to be working..

I also wondered if the fact that the measure I'm trying to use in the calculation part of SUMX contains CALCULATE?

Many thanks!

[v-gizhi-msft]

Hi,

After my test(using all of your sample data and measures), the [Borrowing Forecast] measure should show like this:

So for your requirement, please take following steps:

1)Create a seperate slicer table:

Slicer Table = DISTINCT(SELECTCOLUMNS('Table',"Fiscal Year",'Table'[Fiscal Year]))

2)Try this measure:

Measure =
CALCULATE (
    SUMX ( DISTINCT ( 'Table'[Fiscal Year] ), [Borrowing] * [Multiplier] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[ForecastName] = "Forecast1"
            && 'Table'[Fiscal Year]
                <= SELECTEDVALUE ( 'Slicer Table'[Fiscal Year] ) + 3
            && 'Table'[Fiscal Year] >= SELECTEDVALUE ( 'Slicer Table'[Fiscal Year] )
    )
)

3)The reuslt should show like this:

Tips: Sum of values in 2020 is different from your posted expected result, please check it.

If i have misunderstood your requirement and logic, please for free to let me know.

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto

[Anonymous]

Hi, thanks for the response. Couple of things, one my fault:

the multiplier is fixed after the first few years; so as the selected year changes, it's that and the next 3 years x the same 4 values I initially generated

the forecast needs to be taken from whichever filter is applied outside the measure (coming from a slicer), rather than hard-coded into the measure.

Thanks again for looking at this.

[v-gizhi-msft · Accepted solution]

Hi,

Please try to create a seperate forecastName slicer table first:

ForecastNameSlicer = DISTINCT(SELECTCOLUMNS('Table',"ForeastName",'Table'[ForecastName]))

Then change the original measure to this:

Measure = 
CALCULATE (
    SUMX ( DISTINCT ( 'Table'[Fiscal Year] ), [Borrowing] * [Multiplier] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[ForecastName] = SELECTEDVALUE ( ForecastNameSlicer[ForeastName] )
            && 'Table'[Fiscal Year]
                <= SELECTEDVALUE ( 'Slicer Table'[Fiscal Year] ) + 3
            && 'Table'[Fiscal Year] >= SELECTEDVALUE ( 'Slicer Table'[Fiscal Year] )
    )
)

When select one value in forecastName slicer, the result shows:

Hope this helps.

Best Regards,

Giotto

[FrankAT]

Hi @Anonymous ,

that's what I got, see figure:

Regards FrankAT

[Greg_Deckler]

Generally using ALLEXCEPT or KEEPFILTERS