Average of Min

[manuelbanza]

Hi,

I have this dataset for example:

Hotel	Day	Price
A	1	€   150.00
A	1	€   130.00
A	1	€   200.00
B	1	€   150.00
C	1	€   145.00
C	1	€   135.00
D	1	€   200.00
D	1	€   190.00
A	2	€   190.00
B	2	€   160.00
B	2	€   120.00
C	2	€   120.00
C	2	€   178.00
D	2	€   170.00

So for each day I have Hotels with different prices (different rooms). What I want to do is to calculate the average by hotel of their lowest rate. First calculate the MIN value for each hotel by day and then create the average of those days (in this example day 1 and 2. So then I can have a graph like this:

Or maybe create a column that only return the lowest price for each hotel and the other rows return 0, and then I can create an average of that column excluding zeros.

I have tried a lot of calculations but none of them seem to work. Can you help me with this?

Thank you!

Manuel Banza

[Cesare · Accepted solution]

Try this:

AvgOfMin = AVERAGEX(SUMMARIZE(Table1,Table1[Day],"MinPrice",MIN(Table1[Price])),[MinPrice])

Note that the price needs to be Numeric.

Cesare

[manuelbanza]

@Cesare Thank you so much it works perfectly!

Have a nice weekend.

Manuel Banza

[manuelbanza]

hi @Cesare 

do you know I can calculate the mode of the min(Lowest price)?

thank you,

Manuel Banza