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 Date Country_Name Count 20-May-20 INDIA 20 20-May-20 AMERICA 10 20-May-20 South africa 30 21-May-20 ...
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.

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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks Vianl