Avoid Recurring Value in Table

[Morran]

Hello, everyone. Sorry for interrupting your time, I'd like to ask a question. I'm dealing with the allocation resources problem and I'd like to visualize a summary in the table form. However, my table has redundant values. For example, the table below has redundant excess values "4", Materials "4E", and SourcePlace "B".
Redundant values
Is it possible to show the summary table in Power BI like the following table:

Please kindly find my attached PBI sample files through this link: 
https://drive.google.com/file/d/17JuJsaD2yGODI9gNKrRq1SPDYjOACnMg/view?usp=sharing

I really appreciate your help and thanks to read this post.

Best regards,
Morran

[danextian]

Hi @Morran ,

Just by looking at the screenshots, you might need to use a Matrix instead of a table visua but this may not be the case. Can you please explain your data model? For example, which ones are SourcePlace and Destplace and why there are different tables for the Excess when they have the same set of columns?

[Morran]

Hi @danextian , thanks for responding this post.

So, if you've seen my PBIX file, SourcePlace is places which have the excess items. The amount of these excess items will be allocated to several places who need the items. For example, in the Deficit table, both B and C need "2G", which are "4" and "1"

After that, I want to explore another places that have an excess amount of this "2G".  In this sample, only 1 place that have an excess, which is A. But in the reality, we have plenty source places that have an excess amount of this "2G" that need to be allocated to the shortage places. 
Do have any ideas?

[Morran]

It's similar with supply and demand model, where the "source" warehouse that have an excess amount will supply its units to the "target" warehouse, which is shortage. The idea is to express it using the above table and still thinking about the proper data model and DAX.

[danextian]

Does this mean that regardless of the destination place if a source place has some excess  of a material, it can suppy the deficit? You might be able to achieve your desired result by combining all Excess tables into one using Append and doinng a merge between Material (Excess) and Material (Deficit). Doing that will help you achieve the table below:

Now, the question is if the sum of deficit for each material is more than the sum of Excess, which place and material takes priority? And if there are more than two places with excess of the same material, which one should share supply to the deficit first?

[Morran]

Hi @danextian , Would you mind to share your PBIX file? I think that one is great.

Regards,
Morran

[danextian · Accepted solution]

Hi @Morran ,

I modified the pbix a bit to check for any duplicate Material + Deficit Qty because the merging in Power Query can cause duplicates as in the screenshot below.

The updated logic now returns defici qty only for the first instance.