Distribute values accross all matching

[Anonymous]

I need to reduce the values of a dataset 1 by the results of data set 2.  The problem is that the dataset 1 is more granular than dataset 2.  I was wondering if I make a concatenated field for each dataset that combines the four fields they share, if I can distribute the values of dataset 2 accross all matching rows in dataset 1

I can provide samples of each dataset if needed.

[TomMartens · Accepted solution]

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:

https://support.office.com/en-us/article/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616d1...

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

[I_Like_Pi]

I 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 🙂