Forum Discussion
Calculated average value within filtered parameters
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
3 Replies
THank you for your help!
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
