How calculate Average sale by country and date

[MYDATASTORY]

I  have below data and I would like to calculate Average sales county and month,  I have converted  date as Month slicer which looks like May 2020, May 2021, June 1988, Feb 2020 (DD/MM/YYYY)

ID  Country   Date              Sales

1   USA          1/2/2020       100

2  USA          2/2/2020         200

The average will be (100+200)divide 2 = 150

ID   Country       Date                 Sales

1   USA          1/2/2020       100

2  USA           2/2/2020         200

3  SPAIN       2/05/2020       300

4SPAIN         4/6/1988         100 

I tried below DAX but was not filtering properly when you click two months slicer

Spoiler

Averagex = AVERAGEX(VALUES('Sales Table'[Date]),[Total Sales])

I also tried below,

Country Sales =

Spoiler

CALCULATE(AVERAGEX(

'Sales Table','Sales Table'[Sales]),

FILTER(ALL('Sales Table'),

'Sales Table'[Country ]=SELECTEDVALUE('Sales Table'[Country ])) )

I would like to slice (Slicer) the data by country and Month from date column to be able to get the correct Average even when two months i.e June 1988, Feb 2020 are selected.

Thanks 

[v-easonf-msft · Accepted solution]

Hi , @MYDATASTORY 

Try to create a calculate column first:

Year_month = FORMAT('Sales Table'[Date ],"MMM yy")

Then create a measure as below:

Avg = CALCULATE(AVERAGE('Sales Table'[Sales]),ALLEXCEPT('Sales Table','Sales Table'[Country],'Sales Table'[Year_month]))

 

pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[harshnathani]

Hi @MYDATASTORY ,

Have a look at the below solution

https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-KEEPFILTERS-not-showing-correct-results-a...

Regards,

Harsh Nathani