I have a dataset that I need to transpose and summarise all at once. In Excel this would be easy, but in Dashboards it doesn't seem possible with the standard options as you can only have one category field. So I'll need to use Arcade.
My current dataset looks like this:
Zone | Status | Date Captured (date field) |
Red | Open | Feb 16, 2024 |
Red | Closed | Feb 12, 2024 |
Blue | Open | Feb 01, 2024 |
Red | Open | Feb 16, 2024 |
Yellow | Closed | Feb 13, 2024 |
etc | etc | etc |
But I want to group it by Zone and count the number of status in each one, so it will look something like this:
Zone | Open Count | Closed Count |
Blue | 52 | 6 |
Red | 16 | 8 |
Yellow | 5 | 9 |
I'm trying to use if statements and GroupBy to create new feature sets, but I can't figure out how to transpose the Status field into columns with counts. Any suggestions welcome!