Find the Sales Per Row Per Year Per Channel and Multiple by Factor from lookup Table

[Anonymous]

Hi Experts

See attached sample PBIX

I want to divide the sales for each row by the Total for that Year and By the Channel. So in the FACTSales Add up the total Sales by  Year and by Channel then Divide each row by that Sum. Then Find the Multiple in the Lookuptable and multiple each row by the that mulitpler..

Example. 

Year 2020

Total ALT Sale  =19579

Total DIR Sale = 22259

For each row that has Channel DIR / 2259 and for each row that has ALT / 19579

Then lookup in LOOKUPTable Column Sales U$D find the mulitpler for ALT and Dir in our case ALT= 1.8 and DIR = 18.1 then mutiple each row by that number if its a dir or alt in the sales table

Sample File..

https://www.dropbox.com/s/dclkz5shn3a40gv/test_.pbix?dl=0 

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

please check the below picture and the link down below, whether it is what you are looking for.

https://www.dropbox.com/s/lg43t2e49vabvsw/test_.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Anonymous]

Hi Kim - Can you help with...

https://community.powerbi.com/t5/Desktop/Pro-Rata-based-on-the-End-of-the-Previous-Month/m-p/1780430...

[Anonymous]

Thanks you sir looks good let me test and come back to you