Forum Discussion

Schorse's avatar
Schorse
Regular Visitor
4 years ago
Solved

Read correct price from table with minimum quantities

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!

 

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      Schorse
      Regular Visitor
      4 years ago

      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.

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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.