Forum Discussion
Table showing incorrect total
Total is incorrect. Need help to fix the issue.
Hello CTH2022 can you share the table data instead of the image that would be really helpfull.
| Program | STATION | TYPE |
| A | S1 | F1 |
| A | S1 | F1 |
| A | S2 | F2 |
| A | S2 | F2 |
| A | S1 | F3 |
| A | S2 | F3 |
| A | S2 | F3 |
| A | S1 | F2 |
| A | S1 | F3 |
| A | S2 | F2 |
| A | S2 | F2 |
| A | S1 | F1 |
| A | S1 | F1 |
| A | S2 | F2 |
| B | S2 | F3 |
| B | S1 | F3 |
| B | S2 | F3 |
| B | S2 | F3 |
| B | S1 | F3 |
| B | S1 | F3 |
| B | S2 | F3 |
| B | S2 | F3 |
counting = countax(Sheet1,[TYPE])
top 2 =
var rankingcontext = values(Sheet1[TYPE])
return
calculate([counting],
topn(2,all(Sheet1[TYPE]),
[counting]),rankingcontext)
Wanted to table to show top 2 'TYPE' for each station.
CTH2022 , Try like
top 2 =
var rankingcontext = values(Sheet1[TYPE])
return
Sumx(Summarize(Sheet1, Sheet1[STATION], Sheet1[Program], "_1",
calculate([counting],
topn(2,all(Sheet1[TYPE]),
[counting]),rankingcontext)),[_1])The solution seems to work. However, when I added more data, the table started to display more than top 2 per station. Need help to fix this.
Program STATION TYPE A S1 F1 A S1 F1 A S2 F2 A S2 F2 A S1 F3 A S2 F3 A S2 F3 A S1 F2 A S1 F3 A S2 F2 A S2 F2 A S1 F1 A S1 F1 A S2 F1 B S2 F3 B S1 F3 B S2 F3 B S2 F3 B S1 F3 B S1 F3 B S2 F3 B S2 F3 A S1 F1 A S1 F1 A S2 F2 A S2 F2 A S1 F3 A S2 F3 A S2 F3 A S1 F2 A S1 F3 A S2 F2 A S2 F2 A S1 F1 A S1 F1 A S2 F1 B S2 F3 B S1 F4 B S2 F4 B S2 F5 B S1 F4 B S1 F4 B S2 F5 B S2 F3
