I have a crosstab worksheet displaying customer and total sales by year (Year on columns, Customer on Rows, total sales as text). I also have a grand total for each row (customer), sorted descending on grand total. I would like to add a parameter to allow the user to choose to see the customers that make up the top n% of sales, based off of their grand total. I've started an example in the Superstore workbook, see attached.
kn
Hello there again!
I think I have a solution for you! Please find it attached, with the explanation below:
1. I created a calculated field called "RUNNING_SUM(SUM([Sales]))/TOTAL(SUM([Sales]))", that at the same time:
A. does the running total of SUM(Sales), from the highest to the lowest (since it's sorted)
B. transforms the running total in % to total thanks to the denominator being TOTAL(SUM([Sales]))
if you put that measure onto the measure cards, you'll see exactly what it does
2. Created a list parameter from 1 to 100, intervals each 1 called "Top N% of customers"
3. Created a boolean filed called "Select Top N% of customers", where if the calculated field "RUNNING_SUM(SUM([Sales]))/TOTAL(SUM([Sales]))" is HIGHER than the parameter, it gets filtered out (remember, it's a running total, so you want the lowest values)
4. Finally, I realized that the filter was applied to each individual year if the Order Date field was kept in the column header. I therefore created a calculated field for each year's sale with the formula below:
IF YEAR([Order Date])= 2014/5/6/7 THEN [Sales]
END
5. I used those individual measures as column headers instead of Order Date
BONUS: you could use directly the calc "RUNNING_SUM(SUM([Sales]))/TOTAL(SUM([Sales]))" as a filter, but I prefer parameters, so I chose that option
BONUS II: I added another sheet "tooltip" that kept sales by year that you can see hovering over a customer's information
Let me know if you have any questions!
Carlo
