cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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         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?

Br,

S.

1 ACCEPTED SOLUTION
Super User

Hi, @Anonymous

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.
)

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

8 REPLIES 8
Super User

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

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.

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

Anonymous
Not applicable

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

Super User

Hi, @Anonymous

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

Anonymous
Not applicable
Super User

Hi, @Anonymous

Thank you for sharing.

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.

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

Anonymous
Not applicable

Dear @Jihwan_Kim

I understood almost every part of your syntax.

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
)
Super User

Hi, @Anonymous

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.
)

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

Anonymous
Not applicable

Dear @Jihwan_Kim

Excellent

Thank you very much

S.

Announcements