Stock Calculation

[Anonymous]

Hi everyone, I need your small help. I have two facts tables: -
- One is Current stock report
- One is Min-Max report (how much stock a firm need to maintain per month.

? Need to calculate the current stock in the Min-Max table based on some parameter. 

Have taken one small example to understand the data pattern:-

Min-Stock Need to Maintain
Product	Size	Color	Min-Stock
A	2	black, blue	25
A	3	orange	12
B	2	blue	21
B	3	white, yellow	34

Current Stock
Product	Size	Color	CurrentStock
A	2	black	5
A	3	blue	4
A	2	blue	3
A	3	yellow	4
A	2	yellow	5
B	3	white	7
B	2	organge	8
B	2	blue	5
B	2	black	4
B	3	white	2

Looking for the result as shown below:

Result
Product	Size	Color	Min-Stock	CurrentStock
A	2	black, blue	25	8
A	3	orange	12	0
B	2	blue	21	5
B	3	white, yellow	34	9

Note:- 
For product A, we need to find the color combination of both black and blue in a single cell. We have 1200 products. Need to write one dax function to fetach the corrent data. We can't bifurcation the color combination to two different row. We are fine with any ratio between black and blue. 
Please anyone knows give me the solutions.
@Ruth,  @amitchandak,  @Tahreem24, @Greg_Deckler, @MFelix, @Pragati11, @mwegener 

@mallot86  @Ruth  @avisingh  @Patrik 

@amitchandak @Tahreem24 @Greg_Deckler @MFelix @Pragati11 @mwegener 

@johnbasha33 

[Anonymous]

Thank you @v-yingjl .

[v-yingjl · Accepted solution]

Hi @Anonymous ,

If you want to write dax to achieve this, you can try to create this measure:

_CurrentStock = 
CALCULATE (
    SUM ( 'Current Stock'[CurrentStock] ),
    FILTER (
        'Current Stock',
        'Current Stock'[Product]
            IN DISTINCT ( 'Min-Stock Need to Maintain'[Product] )
                && 'Current Stock'[Size]
                    IN DISTINCT ( 'Min-Stock Need to Maintain'[Size] )
                        && CONTAINSSTRING (
                            SELECTEDVALUE ( 'Min-Stock Need to Maintain'[Color] ),
                            'Current Stock'[Color]
                        )
    )
) + 0

Attached the sample file in the below, hopes it could help.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.