Average of the Monthy Sales WIN Average

[emthib]

Hi! 

I'm looking for a way to compute the Average of the Monthly Sales WIN Average. Sales are classified as WIN, LOST or CANCELLED and I know how many WINs I have each month by using those formulas : 

Average Win QTY per month = AVERAGEX(VALUES('Closed Opportunities with closi'[Close Month]),[Overall_Win])

Overall_Win = CALCULATE(COUNTA('Closed Opportunities with closi'[Win Lost]),'Closed Opportunities with closi'[Win Lost] IN {"Won"})

Now, I would love to do a Control Chart (https://towardsdatascience.com/how-to-create-a-control-chart-in-power-bi-fccc98d3a8f9) to track the amount of WIN since January 2020. I can see that the average of the average is 150 but I can't seem to be able to plot this into a trend graph. Every average formulas I tried gave me 1 as an output. I must not plot the instatenious average (monthly average) in the Control Chart but rather the average over the whole time period (rolling average from Jan 2020 til today). 

Does anyone has an idea in how to achieve that ? 

Thanks everyone 🙂

[emthib · Accepted solution]

Hi Lucien,

Thank you very much for your reply. I actually find my ways afterall using ALLSELECTED to ignore the date context. I coulnd't erase my post (didn't find how). Really sorry about that! Have a lovely day 🙂

[v-luwang-msft]

Hi @emthib ，

Congratulations, you can mark your answer as an answer that may inspire someone with a similar question.

Best Regards

Lucien

[v-luwang-msft]

Hi @ @emthib ，

Could you pls share your pbix file ?

Remember to remove confidential data.

Best Regards

Lucien