1 of 1 people found this helpful
see the attached
it is brute force but will return this
had to play with the parameter and create a week ob date dimension
and then this is the total sales
this is the color calc
and this is the parameter action
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Parameter actions will only allow you to store one value--you'd only be able to filter out one week, but we could use set actions. To do this, instead of Week(Order Date), create a calculated field for the week. Then use that on the columns shelf. Then create a set based on that field.
No need to select any values for now. Then create a set action like this:
So, every time you click a box, it will add the week to the set. To select multiple, you can hold CTRL and then click the boxes.
Next we'll create two measures--one that shows the value for each week and one that shows the overall value. Because we need to display two separate measures, we need to make sure the weekly number is always null for the grand total and the grand total is always null for individual weeks.
// Sales amount for a single week only. // When grand totaled, the count of weeks will be >1 so we'll get a null. IF COUNTD([Week])=1 THEN SUM([Sales]) END
// Separate measure for the grand total. // Count of week will always be 1 for a given week and thus, this will return null. IF COUNTD([Week])<>1 THEN SUM( IF NOT [Week Set] THEN [Sales] END ) END
And we'll create one more to color those fields that you've selected in the set.
// Color based on whether the week is in the set or not. IF [Week Set] THEN 1 ELSE 0 END
Now drag Color to the color card and change the mark type to Square. Click the color card and set to to a custom diverging palette with white on the low end and some color on the high end. Also go to the advanced settings and make sure it starts at 0 and ends at 1.
Then drag Measure Values to the text card, then make sure Total Sales and Weekly Sales are on the Measure Values pane, like shown below:
Now, when you CTRL click the weeks, you should get what you're looking for.
Sample - Superstore.twbx 2.4 MB
Thanks so much. This was a really cool solution that I was able to recreate. The limitations that I am running into are that I cannot have no weeks selected in the view, so there will always be one value of zero. Additionally, I cannot select multiple weeks to exclude. Any ideas on how this could be achieved? I feel that it might be a limitation in the parameter action feature.
Thanks so much for the detailed response. It appears that Set Actions were the answer here. I was able to recreate your solution with no issues. Thanks!
Great! This was a fun one!