Forum Discussion
Distribute values accross all matching
Hey,
I'm not sure if I understand you 100%, because "I need to reduce ..." contradicts "... distribute the values of dataset 2 across ... in dataset 1"
But nevertheless, you can achieve both using Power Query.
Start with Merging queries:
And this may help to reduce dataset 1 by a join condition with dataset 2:
https://docs.microsoft.com/en-us/powerquery-m/joinkind-inner
Hopefully, this gets you started, if not, please prepare an Excel sheet with sample data, one sheet for one dataset and use the 3rd sheet to explain your expected result. Upload the file to onedrive or dropbox and share the link
Regards,
Tom
Resolver III only dabble in PBi and am a kludge king, so this is surely not the best performing way.
In the query editor duplicate DS1 (or reference it i.e. source = DS1) and group on the common field with only a count (consider other metrics if you think they might be useful).
Back in Power BI, relate DS2 to your new summary and build a calculation from the count and the related DS2 value (AdjustmentVal = Divide(Related(DS2Value), Count) assuming you wish to evenly distribute the adjustment. Using Divide you wont get div 0 errors.
Relate DS1 to the Summary and use the adjustment value in a calculated column (Adjusted = Core_Val - Summary[AdjustmentVal])
Good luck :)