Forum Discussion
Calculate Min/Max and Count for each row
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 |
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.
3 Replies
- Greg_Deckler
Community Champion
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.
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.
v-xjiin-msftSolution Sage
Anonymous
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.