Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
70 | |
48 | |
45 | |
19 | |
16 |