topic Re: Have fixed sum fill out entire table in DAX Commands and Tips

Have fixed sum fill out entire table

ThomasSan — Wed, 14 Dec 2022 15:53:12 GMT

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!

Re: Have fixed sum fill out entire table

tamerj1 — Wed, 14 Dec 2022 16:09:05 GMT

Hi ThomasSan

please try

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

Re: Have fixed sum fill out entire table

Thomas_San — Thu, 15 Dec 2022 16:50:35 GMT

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?

Re: Have fixed sum fill out entire table

tamerj1 — Thu, 15 Dec 2022 18:23:16 GMT

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