Average selected date by Count values

[Anonymous]

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	INDIA	25
21-May-20	AMERICA	10
21-May-20	South africa	25
24-May-20	INDIA	30
24-May-20	AMERICA	10
24-May-20	South africa	34

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

[amitchandak]

@Anonymous , Simple Average should have done that, or do you want display against all dates, then you have to use all except : https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

[parry2k]

@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]

Hi Parrys

Thanks your replay.

I have given 3 column its working fine.

but I have 5 column its not working

Date	Country_Name	Count	BPID	RES/NON
20-May-20	INDIA	20	Z10	RES
20-May-20	INDIA	15	Z12	NONRES
20-May-20	AMERICA	10	z12	RES
20-May-20	South africa	30	Z10	RES
21-May-20	INDIA	25	Z10	RES
21-May-20	INDIA	25	Z14	RES
21-May-20	AMERICA	10	Z12	RES
21-May-20	South africa	25	Z15	NONRES
24-May-20	INDIA	30	Z20	NONRES
24-May-20	INDIA	34	Z20	RES
24-May-20	AMERICA	10	Z35	RES
24-May-20	South africa	34	Z10	NONRES

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

Please help on this

Thanks

Shnavitha

[V-lianl-msft · Accepted solution]

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]

Thanks Vianl