cancel
Showing results for
Did you mean:

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

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:

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:

3 REPLIES 3
Super User

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

Regular Visitor

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?

Super User

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors