Calculate Min/Max and Count for each row

[jagostinhoCT]

How to get a visual showing the MIN and MAX areas for each Apartment Code entry from the data in a table such as the one below?

Also, how do I format the %GT Area Apartment Code Total to show w/o decimal cases?

This is what I am aiming to do (fields in red are the ones I am trying to learn how to calculate)

Thanks in advance.

J

Apartment Code	%GT Area Apartment Code Total	Count of Apartment Code	MIN Area	MAX Area
1 bed	20.83%	5	142	603
2 bed	41.67%	10	140	571
3 bed	25.00%	6	123	571
penthouse	12.50%	3	400	571

And this is what I have.

Apartment Code	Area
1 bed	142.8353
1 bed	300.4763
1 bed	310.9436
1 bed	372.24
2 bed	140.3131
2 bed	156.24
2 bed	167.7358
2 bed	171.4803
2 bed	181.7823
2 bed	243.7908
3 bed	123.6074
3 bed	400.2451
3 bed	400.2451
3 bed	405.3767
penthouse	400.2451
penthouse	405.3767
penthouse	571.05
3 bed	405.3767
3 bed	571.05
2 bed	303.8439
2 bed	400.2451
2 bed	405.3767
2 bed	571.05
1 bed	603.1788

[v-xjiin-msft]

@jagostinhoCT

In your scenario, you want to calculate the MAX or MIN areas for each Apartment Code group. Right?

To achieve your requirement, you can create calculated column with expression like:

Min Area =
CALCULATE (
    MIN ( 'Apartment Table'[Area] ),
    FILTER (
        'Apartment Table',
        'Apartment Table'[Apartment Code]
            = EARLIER ( 'Apartment Table'[Apartment Code] )
    )
)

MAX Area =
CALCULATE (
    MAX ( 'Apartment Table'[Area] ),
    FILTER (
        'Apartment Table',
        'Apartment Table'[Apartment Code]
            = EARLIER ( 'Apartment Table'[Apartment Code] )
    )
)

The result shows like this:

Thanks,
Xi Jin.

[Greg_Deckler · Accepted solution]

Use the format area on the Modeling tab to decrease the number of decimal places to 0. Hit the up arrow because it is probably set to Auto at the moment.

I believe that the two measures that you want are:

MIN Area = MIN(Table[Area])

MAX Area = SUM(Table[Area])

If you put those in a table along with your [Apartment Code] you should get what you want.

[jagostinhoCT]

@Greg_Deckler

I do not think that the Modeling tab formatting applies to the % of Grant Total. See image below.

The MIN and MAX work perfectly. So easy. So simple. Sometimes one gets bogged down with complicating things unnecessarily.

May thanks.