topic Re: Conditional daily average in DAX Commands and Tips

Conditional daily average

Anonymous — Thu, 10 Jun 2021 12:18:05 GMT

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,

Re: Conditional daily average

Jihwan_Kim — Thu, 10 Jun 2021 12:56:26 GMT

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

Re: Conditional daily average

Anonymous — Thu, 10 Jun 2021 13:22:13 GMT

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

Re: Conditional daily average

Jihwan_Kim — Thu, 10 Jun 2021 13:26:07 GMT

Hi, Anonymous

Please share your sample pbix file's link here.

Re: Conditional daily average

Anonymous — Thu, 10 Jun 2021 13:47:10 GMT

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

Re: Conditional daily average

Jihwan_Kim — Thu, 10 Jun 2021 14:19:13 GMT

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

Re: Conditional daily average

Anonymous — Thu, 10 Jun 2021 17:17:14 GMT

Dear Jihwan_Kim

I understood almost every part of your syntax.

Can you please help me with understanding the red highlighted parts?

Thank you

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

)

Re: Conditional daily average

Jihwan_Kim — Fri, 11 Jun 2021 02:29:38 GMT

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.

)

Re: Conditional daily average

Anonymous — Fri, 11 Jun 2021 05:42:54 GMT

Dear Jihwan_Kim

Excellent

Thank you very much