Forum Discussion
Finding Top Columns Based on First Column
"I'm trying to find a way to show the top 3 based on the current filters applied to the report."
Top 3 based on what criteria? Based on the Average across the row? Why and average? Why not simply the count of 1's for any one row.
Assume the columns are INT, (NOT boolean), then you could have a Calculated Column like this:
[Number of YES] = [Column1] + [Column2] + ... + [Column50]
If you really need the average, then: ( [Column1] + [Column2] + ... + [Column50] ) / 50
Or, better: DIVIDE ( : ( [Column1] + [Column2] + ... + [Column50] ) , 50, 0 )
Once you have that, can you not simply do a TOP N Filter on the visual and use that calculated column?
Am I missing something?
I want the top three columns as in the column with the highest average (average of all numbers in the column, not across the row). I need the average for each column because 1 indicates yes for that row in that category so an average will give me a percentage of records that hit for that category. I could just average the columns beforehand but if I apply filters from my other tables, it would still show the overall percentage, not the percentage for the applied filters.