Forum Discussion
PaulHallam
Helper III
Helper IIIAdding a column to return if data is a maximum per category
Hi,
Looking for help.
I have a data table sample below.
| City | Building | Category | Date | Day | ID | Return 1 if it’s the Highest Category 2 Day |
| London | A | Category 1 | 05/09/2022 | Monday | A | |
| London | A | Category 1 | 05/09/2022 | Monday | B | |
| London | A | Category 2 | 05/09/2022 | Monday | C | |
| London | A | Category 3 | 05/09/2022 | Monday | D | |
| London | A | Category 1 | 06/09/2022 | Tuesday | A | 1 |
| London | A | Category 2 | 06/09/2022 | Tuesday | B | 1 |
| London | A | Category 3 | 06/09/2022 | Tuesday | C | 1 |
| London | A | Category 2 | 06/09/2022 | Tuesday | D | 1 |
| London | A | Category 2 | 07/09/2022 | Wednesday | A | |
| London | A | Category 3 | 07/09/2022 | Wednesday | B | |
| London | A | Category 1 | 07/09/2022 | Wednesday | C | |
| London | A | Category 2 | 07/09/2022 | Wednesday | D | |
| London | A | Category 1 | 08/09/2022 | Thursday | A | 1 |
| London | A | Category 3 | 08/09/2022 | Thursday | B | 1 |
| London | A | Category 2 | 08/09/2022 | Thursday | C | 1 |
| London | A | Category 2 | 08/09/2022 | Thursday | D | 1 |
| London | A | Category 3 | 09/09/2022 | Friday | A | 1 |
| London | A | Category 2 | 09/09/2022 | Friday | B | 1 |
| London | A | Category 3 | 09/09/2022 | Friday | C | 1 |
| London | A | Category 3 | 09/09/2022 | Friday | D | 1 |
| London | A | Category 2 | 12/09/2022 | Monday | A | 1 |
| London | A | Category 2 | 12/09/2022 | Monday | B | 1 |
| London | A | Category 1 | 12/09/2022 | Monday | C | 1 |
| London | A | Category 1 | 12/09/2022 | Monday | D | 1 |
| London | A | Category 1 | 13/09/2022 | Tuesday | A | |
| London | A | Category 2 | 13/09/2022 | Tuesday | B | |
| London | A | Category 1 | 13/09/2022 | Tuesday | C | |
| London | A | Category 1 | 13/09/2022 | Tuesday | D | |
| London | A | Category 2 | 14/09/2022 | Wednesday | A | 1 |
| London | A | Category 2 | 14/09/2022 | Wednesday | B | 1 |
| London | A | Category 2 | 14/09/2022 | Wednesday | C | 1 |
| London | A | Category 3 | 14/09/2022 | Wednesday | D | 1 |
| London | A | Category 1 | 15/09/2022 | Thursday | A | |
| London | A | Category 1 | 15/09/2022 | Thursday | B | |
| London | A | Category 3 | 15/09/2022 | Thursday | C | |
| London | A | Category 3 | 15/09/2022 | Thursday | D | |
| London | A | Category 3 | 16/09/2022 | Friday | A | |
| London | A | Category 1 | 16/09/2022 | Friday | B | |
| London | A | Category 1 | 16/09/2022 | Friday | C | |
| London | A | Category 1 | 16/09/2022 | Friday | D |
I am trying to add the last column 'Return 1 if it’s the Highest Category 2 Day', filled in as an example.
So
Look at all Mondays and see which Monday has the highest number of Category 2's. Then to return a 1 in the column in all of the columns for that date. etc etc for all days. So if i had a years worth of data only one Monday would have 1's one Tuesday etc and these would be the days with the maximum Category 2's
Hope that makes sense
P
Try these calculated columns:
Category 2 Row Count = VAR vDay = Table1[Day] VAR vDate = Table1[Date] VAR vTable = FILTER ( Table1, Table1[Day] = vDay && Table1[Date] = vDate && Table1[Category] = "Category 2" ) VAR vResult = COUNTROWS ( vTable ) RETURN vResultIs Highest Category 2 Day = VAR vDay = Table1[Day] VAR vMaxRowCount = MAXX ( FILTER ( Table1, Table1[Day] = vDay ), Table1[Category 2 Row Count] ) VAR vResult = IF ( Table1[Day] = vDay && Table1[Category 2 Row Count] = vMaxRowCount, 1 ) RETURN vResult
2 Replies
- DataInsights
Super User
Try these calculated columns:
Category 2 Row Count = VAR vDay = Table1[Day] VAR vDate = Table1[Date] VAR vTable = FILTER ( Table1, Table1[Day] = vDay && Table1[Date] = vDate && Table1[Category] = "Category 2" ) VAR vResult = COUNTROWS ( vTable ) RETURN vResultIs Highest Category 2 Day = VAR vDay = Table1[Day] VAR vMaxRowCount = MAXX ( FILTER ( Table1, Table1[Day] = vDay ), Table1[Category 2 Row Count] ) VAR vResult = IF ( Table1[Day] = vDay && Table1[Category 2 Row Count] = vMaxRowCount, 1 ) RETURN vResult
PaulHallamThanks for such a quick reply. I think i follow the logic......fantastic.!