Conditional daily average

[Anonymous]

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         Conditional average

2021.06.01        1               0

2021.06.02        2               0

2021.06.03        4               4

2021.06.04        6               5

2021.06.05        2               5

What I would like to achieve is in column "Conditional average".

Do you have any suggestion of dax systanx what can give the awaited result?

Thank you in advance

Br,

S.

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

Conditional Average Measure =

AVERAGEX (

FILTER (

ALLSELECTED ( Sales ),

Sales[Date] <= MAX ( Dates[Date] )

&& Sales[Sales] > 2

),

Sales[Sales]

)+0

https://www.dropbox.com/s/gncg2voxlhkntz4/sandor.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Hi @Jihwan_Kim !

I forgot to say, that the "sales" column is a measure already (sum of sales). Thus it is not working inside the Allselected function.

Do you have any idea for this alteration?

Thank you

S

[Jihwan_Kim]

Hi, @Anonymous 

Please share your sample pbix file's link here.

[Anonymous]

https://www.dropbox.com/s/c7x6mnftduksx9b/Average.pbix?dl=0

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for sharing.

Please check the link down below.

https://www.dropbox.com/s/im0kkz3yajo89qn/Average.pbix?dl=0 

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, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

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

)

[Jihwan_Kim · Accepted solution]

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.

)

[Anonymous]

Dear @Jihwan_Kim  

Excellent 

Thank you very much

S.