I have a spreadsheet where I am adding all the values related to customer groups as per each month, now each of the values contains a method of calculating formulas, I have defined the process below:
For Example if we talk about MGTL Dealers Customer Group, first I will calculate its total overall sales as per Sales Group "Channel Sales OEM/FWS" and Customer Group "MGTL Dealers", next from my spreadsheet I will pick up the months, now I have made the relationship using Month/Year with both the tables (My main query ZSD_Mat... and Month Wise Sales), This will give me total Sales Volume for that period (Example April),
Secondly I want to multiply the value that is present in Month Wise Sales Sheet (For MGTL Dealers) for selected April Month to my above calculation (DAX should be automated so that for each month I should get these current values).
Thirdly after getting this value I want to divide the above value and my 1st values together.
Now the main problem is how can I pick up the values from the excel sheet for each of the customer groups so that they can get multiplied correctly and I can divide with 1 single formula to get to the desired results (the excel sheet is just an overview of what I have, like this I have 100 customer groups that I want to incude months of 2019, 2018, Not sure how to use Many to Many relationship here, any suggesstions will surely help me.
You can look at the sample formulas that I have used to calculate in above ways but with constant values that are not changing each month, but problem is of dynamic data which changes each month (EX:LEY POWER SER DEALER ROYALTY Measure)
Attaching my PowerBi and Excel Spreadsheet (Look for Month Wise Royalty Sheet in the excel).
Spreadsheet details: https://ifitech-my.sharepoint.com/:x:/g/personal/anmol_ganju_ifi_tech/EWR3aZW2qUNEiqu9sgggyL8Bx5Falk...
PowerBi PBIX: https://ifitech-my.sharepoint.com/:u:/g/personal/anmol_ganju_ifi_tech/EVLaKcU7xmhNt4UHutju6soBdQnGdP...
