Calculated average value within filtered parameters

[vicsinc]

I am trying to create a visual which shows actual cost per sale by brand against target, filterable by month and by brand.

I am struggling with the DAX for the measure for the true cost per sale over a filtered period of time - ie it is the sum of the spend for those months divided by the sum of the sales for those months. I thought it would be straight forward but nothing I've tried works.

Here's the sample data. I'd really appreciate any help as I have a total mind block with this now.

Brand	Month	Spend	Sales	CPS	Target
Brand1	Jan-22	£16,505.46	985	£16.76	£21.00
Brand1	Feb-22	£7,558.00	845	£8.94	£21.00
Brand2	Jan-22	£5,632.64	183	£30.78	£67.00
Brand2	Feb-22	£1,972.25	110	£17.93	£67.00
Brand3	Jan-22	£16,413.63	614	£26.73	£52.00
Brand3	Feb-22	£8,211.31	427	£19.23	£52.00

Many thanks

Victoria

[v-yanjiang-msft]

Hi @vicsinc ,

Does your problem been solved by @amitchandak 's solution, if yes, please consider accept it as the solutionto help the other members find it more quickly. If not, could you please show your expected result?

Best Regards,
Community Support Team _ kalyj

[amitchandak]

@vicsinc , Try a measure like

avergageX(Values('Date'[Date]), calculate(sum(Table[Spend])))

[vicsinc]

THank you for your help!