Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Average selected date by Count values

HI Team

I have 2 filter my report below

I have table below format

DateCountry_NameCount
20-May-20INDIA20
20-May-20AMERICA10
20-May-20South africa30
21-May-20INDIA25
21-May-20AMERICA10
21-May-20South africa25
24-May-20INDIA30
24-May-20AMERICA10
24-May-20South africa34

 

IF User select 20-may-2020 to  26-may-2020 but data didnt avialble 23,25,26 dates

INDIA avaerage=(20+25+30)/3 =28.3( sum of count divided by No of data availlable date)

similar Amrica and South africa

 

please anybody help this

 

 

Thanks

Shanvitha

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    Hi Anonymous ,

     

    You can try measure like this:

    average_count =
VAR RunningTotal =
    CALCULATE (
        SUM ( 'Table'[Count] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            SUMX (
                FILTER (
                    ( 'Table' ),
                    EARLIER ( 'Table'[Country_Name] ) = 'Table'[Country_Name]
                ),
                'Table'[Count]
            )
        )
    )
VAR distinct_day =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
RETURN
    DIVIDE ( RunningTotal, distinct_day )

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous why you are not using simple average function in a measure 

     

    Avg = AVERAGE ( Table[Count] )

     

    In table visual, use country and above measure, and you will get the result.

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Parrys

      Thanks your replay.

      I have given 3 column its working fine.

      but I have 5 column its not working

       

      DateCountry_NameCountBPIDRES/NON
      20-May-20INDIA20Z10RES
      20-May-20INDIA15Z12NONRES
      20-May-20AMERICA10z12RES
      20-May-20South africa30Z10RES
      21-May-20INDIA25Z10RES
      21-May-20INDIA25Z14RES
      21-May-20AMERICA10Z12RES
      21-May-20South africa25Z15NONRES
      24-May-20INDIA30Z20NONRES
      24-May-20INDIA34Z20RES
      24-May-20AMERICA10Z35RES
      24-May-20South africa34Z10NONRES

       

      ABove table INDIA output =(20+15+25+25+30+34)/2=49.6

      Please help on this

       

       

      Thanks

      Shnavitha

       

       

      • V-lianl-msft's avatar
        V-lianl-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        You can try measure like this:

        average_count =
VAR RunningTotal =
    CALCULATE (
        SUM ( 'Table'[Count] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            SUMX (
                FILTER (
                    ( 'Table' ),
                    EARLIER ( 'Table'[Country_Name] ) = 'Table'[Country_Name]
                ),
                'Table'[Count]
            )
        )
    )
VAR distinct_day =
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
RETURN
    DIVIDE ( RunningTotal, distinct_day )

         

        Best Regards,
        Liang
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.