Forum Discussion
Conditional daily average
Dear All! I would like to kindly ask for your help with the following. I would like to calculate the cummulative avarege of daily sales higher, than 2. Date Sales Condi...
Hi, Anonymous
Thank you for your feedback.
Please check the below.
The best way to understand it is to remove it from the formula and compare the results.
Conditional Average Measure =IF (ISFILTERED ( 'Date table'[Date] ), -- In the table visualization, total row shows irrelevant information. This is not to show the total row in the table visualization.AVERAGEX (FILTER (SUMMARIZE ( ALL ( Sales ), Sales[Date], "@salestotal", [Sum] ),[@salestotal] > 2&& Sales[Date] <= MAX ( 'Date table'[Date] ) -- This is restricting the range of dates that are less than each row context in the table visualization.),[@salestotal]) + 0 -- The first row and the second row does not show anything without this.)
Dear Jihwan_Kim
I understood almost every part of your syntax.
Can you please help me with understanding the red highlighted parts?
Thank you
S.
Conditional Average Measure =
IF (
ISFILTERED ( 'Date table'[Date] ),
AVERAGEX (
FILTER (
SUMMARIZE ( ALL ( Sales ), Sales[Date], "@salestotal", [Sum] ),
[@salestotal] > 2
&& Sales[Date] <= MAX ( 'Date table'[Date] )
),
[@salestotal]
) + 0
)
Hi, Anonymous
Thank you for your feedback.
Please check the below.
The best way to understand it is to remove it from the formula and compare the results.
Conditional Average Measure =
IF (
ISFILTERED ( 'Date table'[Date] ), -- In the table visualization, total row shows irrelevant information. This is not to show the total row in the table visualization.
AVERAGEX (
FILTER (
SUMMARIZE ( ALL ( Sales ), Sales[Date], "@salestotal", [Sum] ),
[@salestotal] > 2
&& Sales[Date] <= MAX ( 'Date table'[Date] ) -- This is restricting the range of dates that are less than each row context in the table visualization.
),
[@salestotal]
) + 0 -- The first row and the second row does not show anything without this.
)