cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Average of Min

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

1 ACCEPTED SOLUTION
Regular Visitor

Try this:

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

Note that the price needs to be Numeric.

Cesare
3 REPLIES 3
Regular Visitor

Try this:

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

Note that the price needs to be Numeric.

Cesare
Frequent Visitor

@Cesare Thank you so much it works perfectly!

Have a nice weekend.

Manuel Banza

Frequent Visitor

hi @Cesare

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

thank you,

Manuel Banza

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.