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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ThomasSan
Helper III
Helper III

Have fixed sum fill out entire table

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:

ThomasSan_0-1671032923495.png

 

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:

ThomasSan_1-1671032996533.png

 

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

 

Thank you in advance!

 

3 REPLIES 3
tamerj1
Super User
Super User

Hi @ThomasSan 

please try

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

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:

Thomas_San_0-1671122975734.png

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?

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors