forecast by customer ratio

[adsam]

Hi Champions, 

I have Sales data @ Product, Location, Week & Customer level

and forecast data @ Product, Location, Week level

what I want is to get forecast * customer sales ratio . so forecast will also come @ sales level.

In below snapshot yellow highlighted field is required. 

I tried this query but doesn't seems to be working

FC by Customer = VAR ProdSls=ADDCOLUMNS(Prod_Mstr,"Prdsls",[Sales])
Return SUMX(ADDCOLUMNS(CROSSJOIN(ProdSls,Location_mstr,Customer),"Sls",[Sales]),DIVIDE([Sls],[Prdsls])*[Forecast])

File Link:

[lbendlin]

Thank you for the pbix. Please see attached for a sample implementation.

[adsam]

thanks for your reply, but still not solved. customer forecast data still @ total level, and customer forecast coming till sales horizon, means till October, 

current forecast data is @ Product & location level, what I want is to find customer sales % over selected location and product, then multiply forecast to that % in that way I can get forecast @ customer level. 

[lbendlin]

That's what I provided on page 2? Are you looking for an extrapolation?

[adsam · Accepted solution]

thanks Ibendlin for your time and help. 

below orange line I wanted

I used below query and my issue resolved. 

Forecast by Customer = VAR Allsales=CALCULATE(Sales_Trans[Sales],ALL(Customer),ALL('Calendar'))
                        VAR CusSales=CALCULATE(Sales_Trans[Sales],ALL('Calendar'))
                            Return DIVIDE(CusSales,Allsales)*[Forecast]

File link: