I have a dashboard that shows total sales over time by salesperson ID. I have a date in the column shelf and an aggregate (SUM of sales) in the rows shelf. I have a date filter to limit the results to the previous X days.
I want to show only those salespeople who have at least one day in the date range with sum(sales) greater than a threshold parameter called "Sales Threshold."
Right now I have a filter that limits ID by MAX([sales])>=[Sales Threshold]. I believe this is limiting the result to members who have had one or more individual sales greater than the threshold parameter. How do I:
1. Limit the results to only IDs with a day's total sales surpassing the threshold
2. Limit the threshold test to the date range of interest
I have searched for other people asking the same question. I think the solution involves LOD expressions and/or Window_Max but I have been unable to make either solutions work.
Has anyone dealt with a similar issue?
Thanks,
Chris
I think this is what you're going for. Here's what I did:
1. Right-click the ID filter and select Remove.
2. Right-click the Date filter and select Add to Context.
3. Create a new calculated field (I called it "Filter") with the following formula:
IF {FIXED [ID] : MAX({FIXED [ID], [Date] : SUM([Sales])})}>=[Sales Threshold]
THEN "YES"
ELSE "NO"
END
4. Drag Filter to Filters and select YES then click OK.
