Question:
I'm looking for a way to count number of records below and above median/AVG.
Answer:
Step 1: Add all category columns (Country, City, ID) to the Rows panel.
Step 2: Add a column under the Value panel that calculates the total amount. The formula should look like this: SUM(Amount). Save this column as a favorite and call it Total Amount.
Step 3: Add another column to calculate the median/average of the amount per Country and City. Use the shortcut created in Step 2, so the formula should look like this: MEDIAN([ID], [Total Amount]). Save this column as a favorite and call it Median Amount.
Step 4: Add another column to evaluate if the [Total Amount] of each Country and City is below/above the median/average. Use the shortcut created in Step 2 & 3, so the formula should look like this: IF([Total Amount] < [Median Amount], 1, 0). Save this column as a favorite and call it Below Median?.
Step 5: Add the last column to count the number of records that fall below/above the median/average. Use the shortcut created in Step 4, so the formula should look like this: SUM([ID], [Below Median?]). Rename the column as Number of Records Below Median.
Step 6: Verify all numbers are correct. Disable all columns that are not to be displayed, for example: keep only Country, City, Total Amount, Median Amount, Number of Records Below Median.
You can follow the same instructions to get number of records above median, below/above average, and so on. You just need to modify the aggregation (step 3) and the IF statement (step 4).
