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