Comparing prices based on scale price (different quantities)

[Anonymous]

Hello,

please I need your support to solve my problem.

I want to compare actual price/unit (Revenues) with price/ unit (scale price) based on quantity. The scale price based on different quantity. I have 2 different tables. The first one shows the revenues and fees for each product. As well price/ unit (Revenues divided by quantity). The second table contains scale price based on different quantities.

This is how it should look like in the end:

First table revenues:

Second table Scale prices:

In Excel i would solve this one with VBA and a loop, but in Power BI i have no clue how to solve my problem.

I am thankfull for every help or solution I can get!

Thanks for your help!!

freiburgc

[amitchandak]

You can create a measure using lookup. or can try using create a column in table 1 like this

Max price= Maxx(filter(Table2,Table1[product]='Item'[product] && Table1[posting date]>='table2'[posting date]),table2[price ]) 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous · Accepted solution]

@amitchandakthanks for your answer. But unfortunately I don´t understand your solution.

I try to make my problem more clear. Maybe it helps.

This is how should look like in the end:

For example:

The price for product B, Revenues 180€, is 30€ if someone buys 6 units. If someone gets for e.g. 15 units, the price is 28€. All over 11 units the price is 28€.

1. Does it make sense to work in the table scale price with range over two columns or is one column for units enough?

Scale price table:

Do you have another idea to solve my problem?

Thanks for your support!

Freiburgc