Have fixed sum fill out entire table

[ThomasSan]

Hi everyone,

I am trying to create a chart where all figures are normalized at the start point in order to track the relative deviation from it over time. It should look as follows:

Now, I tried to use the following DAX command:

100% = 
var curr =
CALCULATE(
    sum(Table[SalesTotalEUR]),
    KEEPFILTERS(
        Table[Date]
    )
)

var refer =
CALCULATE(
    sum(Table[SalesTotalEUR]),
    FILTER(
        Table,
        Table[Date].[Year] = 2021 && Table[Date].[MonthNo] =1
    )
)

var div=
DIVIDE(
    curr,
    refer,
    ""
)

return div  

However, it results in only row January 2021 being filled out as can be seen here:

I just cannot figure out where my error is. Can someone please help me here?

Thank you in advance!

[tamerj1]

Hi @ThomasSan 

please try

VAR refer =
CALCULATE(
SUM ( Table[SalesTotalEUR] ),
FILTER(
All ( Table[Date] ),
Table[Date].[Year] = 2021 && Table[Date].[MonthNo] = 1
)
)

[Thomas_San]

Thank you for your reply, @tamerj1 !

I have modified my command according to your recommendation and it looks as follows:

100% = 
var curr =
CALCULATE(
    sum('Table'[SalesTotalEUR]),
    KEEPFILTERS(
        'Table'[Date]
    )
)

var refer =
CALCULATE(
    sum('Table'[SalesTotalEUR]),
    FILTER(
        All('Table'[Date]),
        YEAR('Table'[Date])=2021 && MONTH('Table'[Date])=1
    )
)

var div=
DIVIDE(
    curr,
    refer,
    ""
)

return div

As you can see, I was not able to recreate the line Table[Date].[Year] = 2021 && Table[Date].[MonthNo] = 1 but my workaround should not alter the result.

Unfortunately, my result is still as follows:

The underlying table is a flat table i.e. the date column is in the same column as the SalesTotalEUR column.

Do you know what might be the issue here?

[tamerj1]

Hi @Thomas_San 

are you using a date table? If not please try

100% =
VAR curr =
SUM ( 'Table'[SalesTotalEUR] )
VAR refer =
CALCULATE (
SUM ( 'Table'[SalesTotalEUR] ),
YEAR ( 'Table'[Date] ) = 2021
&& MONTH ( 'Table'[Date] ) = 1,
ALLEXCEPT ( 'Table', 'Table'[Country Code] )
)
VAR div =
DIVIDE ( curr, refer, "" )
RETURN
div