Read correct price from table with minimum quantities

[Schorse]

Hello all,

I have a (hopefully simple) problem that you guys can probably help me with....

I want to read out the corresponding price from a table with prices via Measure.

My problem is that in the SalesPrice table there are only the minimum quantities from which a certain price is valid.
If there would be also a maximum value, I would manage that. At least I think so.

The SalesPrice table is structured as follows:

ItemNo.  |  MinQuantity  |  Price
4711       | 1.000               |  3.99
4711       |  10.000            |  2,99
5888       |  2.000              |  1.59
5888       |  3.000              |  1.45
...             |  ...                    |  ....

Does anyone have a solution for me there? Or do you need more info?

Thanks a lot!

[v-chenwuz-msft · Accepted solution]

Hi @Schorse ,

Have your problem solved?

If yes, please make that post helpful to you, as a solution. So that others have the same problems as you can find the answer quickly.

If no, maybe you can try this measure.

Measure =
VAR _minq =
    CALCULATE(
        MIN( 'Table'[MinQuantity] ),
        ALLEXCEPT(
            'Table',
            'Table'[ItemNo.]
        )
    )
RETURN
    CALCULATE(
        MIN( 'Table'[Price] ),
        FILTER(
            ALLEXCEPT(
                'Table',
                'Table'[ItemNo.]
            ),
            [MinQuantity] = _minq
        )
    )

Result:

If i misunderstood you ,please share you pbix file without sensitive data and expect result.

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.

[amitchandak]

@Schorse , it is not clear what price you want to read

a new column with min price

minx(filter(Table, [ItemNo] =earlier([ItemNo]) ) , [Price] )

[Schorse]

Hello @amitchandak,

I have another table with sales quantities to the items.

And now I want to find out the corresponding price by measure.

So I don't need the minimum price per item, but the price per item for a certain retrieved quantity.

sorry, that I should have certainly still write there.

[amitchandak]

@Schorse , we can create columns across tables but need sample data for that

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Schorse]

Hello @amitchandak 

thank you very much for your support.

I just want to insert the correct price in the Sales table.

I am not able to upload a file. Therefore, I would just need a formula that will fill me the Price column in the Sales table....

Item	Quantity	Price
4711	1500	?
4711	20000	?
5888	2000	?
5888	3500	?

Sales-Price-Table:

ItemNo	MinQuantity	Price
4711	1000	3,99
4711	10000	2,99
5888	2000	1,59
5888	3000	1,45

Thanks a lot
Schorse

[amitchandak]

@Schorse  You can copy paste some sample here from excel or power bi

or load a file on drop box or onedrive and share link