Forum Discussion
ALL() within Summarize()
Hello,
I have a table FACT_Sales that contains the columns
Article | Calendar Week | Year | | Store | Units sold
In a dashboard the user can use a filter to select the current calendar week and year.
In a table I now want to visualize per article:
- the average number of stores where a given article has been sold per week
So for example:
- User selets CW48 and year 2024
- measure logic: group values by all calendar weeks, count rows of column store or units. Average over countes rows for all cws.
I hope I explained this well.
My measure which looks like this does not give me the average over alle calendar weeks but only the value for the currently selected Calendar week.
AVERAGEX(
SUMMARIZE(
CALCULATETABLE(
FACT_Sales,
ALL(FACT_Sales[CW]),
),
FACT_Migros_Sales[CW],
"AGG_Value", COUNT(FACT_Sales[Units Sold])
),
[AGG_Value]
)
Looking forward to your tips :))
okay so to be honest the issue was with my date table structure. I have a complex structure in order to be able to swith between calendar year and fiscal year and it did not work because I was not referencing the right date table. So all the approaches including mine do work in the simplified scneario.
5 Replies
Hi Trisulara88 - you need to override the current filter on Calendar Week and ensure the calculation includes all weeks.
AvgStoresPerWeek =
AVERAGEX(
SUMMARIZE(
CALCULATETABLE(
FACT_Sales,
ALL(FACT_Sales[Calendar Week]) -- Ignore the Calendar Week filter
),
FACT_Sales[Calendar Week], -- Group by Calendar Week
"StoreCount", DISTINCTCOUNT(FACT_Sales[Store]) -- Count distinct stores
),
[StoreCount] -- Average the store count per week
)Hope this works in your scenerio
Hi Trisulara88 ,
Please try the bellow measure, and let me know if it works:
Average Stores Per Article = AVERAGEX( SUMMARIZE( CALCULATETABLE( FACT_Sales, ALL(FACT_Sales[Calendar Week]) -- Remove any filter on Calendar Week ), FACT_Sales[Article], -- Group by Article FACT_Sales[Calendar Week], -- Group by Calendar Week "Store Count", COUNTROWS(DISTINCT(FACT_Sales[Store])) -- Count distinct stores per week ), [Store Count] -- Take the average of the Store Count across all weeks )Hi,
Share some data to work with and show the expected result. Also, it is a best practise to create a Calendar Table with a week number column in there.
Thank you. The two proposed solutions are basically what I'm doing already, but somehow the All(Fact_Sales[Calendar Week]) inside Calculatetable fails to remove the filter on Calendar Week... Not sure why this is happening
okay so to be honest the issue was with my date table structure. I have a complex structure in order to be able to swith between calendar year and fiscal year and it did not work because I was not referencing the right date table. So all the approaches including mine do work in the simplified scneario.
