Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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.
    )
Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Jihwan_Kim
      Super User
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        Dear Jihwan_Kim  

        Excellent 

        Thank you very much

        S.