I want to model data with these two worksheets .

[powerbighy]

Dear all,

I have two worksheets. One contains product category and requested quantity,  and the other contains product category and SN of the available stock of the products .

I want to model data with these two worksheets . 

And when I select the category A in the first table ,it will display the SN in the second table base on the requested quantity.

If I select the first category E in the first table ,it displays the SN of the first category E and if I select the second category E, it display the SN of the second Category E .

This scenario is about allocating available stock in the second table to every request in the first table. 

How should I model data ? 

Thanks a lot.

[amitchandak]

@powerbighy , Create a measure like

Sumx(Values(Table1[Category]), calculate(Divide(Sum(Table[Qty]), Count(Table2[Product SN]))))

Make sure two tables are joined on category

[powerbighy]

Hi Amitchandak,

Do you mean I need to create many-to-many relationships between these two lables ?

And create a measure with the code you provided.

[amitchandak]

@powerbighy , Yes, Try that out

[powerbighy]

Hi Amitchandak，

I think I didn't make myself clear.

I want to get the result as follows based on those two worksheets.

the quantity of A is one，so it will get one SN of category A from table 2

the quantity of B is two，so it will get two  SN  of category B from table2

[v-chenwuz-msft · Accepted solution]

Hi @powerbighy,

Do not create relationship between these tables and try the following measure to do that.

Measured =
VAR _quantity =
    SUM ( quantity[quantity] )
VAR _1 =
    TOPN (
        _quantity,
        FILTER ( 'SN', [product category] = SELECTEDVALUE ( quantity[category] ) ),
        [Index], ASC
    )
RETURN
    CONCATENATEX ( _1, [product SN], "
" )

Result:

If you want a normal table without two line in one row, which like below.

Use this measure:\

i =
IF (
    SELECTEDVALUE ( SN[product SN] )
        IN CALCULATETABLE (
            VALUES ( SN[product SN] ),
            TOPN (
                SUM ( quantity[quantity] ),
                FILTER (
                    ALL ( 'SN' ),
                    [product category] = SELECTEDVALUE ( quantity[category] )
                ),
                [Index], ASC
            )
        ),
    " "
)

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[powerbighy]

Hi Chenwuz，

If I add a column named request date. How can I group them also by request date？

Thanks.

[powerbighy]

Dear Chenwuz,

Thanks for your help.

If I add a column to indicate if the product is available just like the following screenshot.

Let me put it another way. The result should use the available（1） product for the relevant category if the available products are not enough， then use the unavailable（0） ones