Forum Discussion
Solved
Reference Maximum Text into table visual
Hi,
I am wanting to create a table visual (as per below) for Top 5 series with measures. I have been successful except for 'most active state' calculation (refer circled). Below is the data used to summarise the table. It takes top 5 series (based on qty), total quantity for that series, most active state quantity, and the most active state (based on quantity).
I have tried using Maxx to bring most active state without luck. Please help
| Series | State | Volume |
| A | NSW | 21 |
| A | SA | 60 |
| A | VIC | 7 |
| B | VIC | 50 |
| C | NSW | 10 |
| D | NSW | 7 |
| E | NSW | 22 |
| E | QLD | 177 |
| F | VIC | 19 |
| G | NSW | 50 |
| H | NSW | 10 |
| I | NSW | 19 |
| I | VIC | 1950 |
| J | NSW | 820 |
| J | VIC | 7 |
| K | QLD | 11 |
| L | NSW | 14 |
| M | SA | 8 |
| N | NSW | 16 |
| N | VIC | 155 |
Give this a try.
Most Active State = FIRSTNONBLANK ( TOPN ( 1, VALUES ( 'Table'[State] ), CALCULATE ( SUM ( 'Table'[Volume] ) ) ), 1 )
2 Replies
Give this a try.
Most Active State = FIRSTNONBLANK ( TOPN ( 1, VALUES ( 'Table'[State] ), CALCULATE ( SUM ( 'Table'[Volume] ) ) ), 1 )Thanks worked a treat
