5 Replies Latest reply on Jan 9, 2020 2:24 PM by Ken Flerlage

# Highlight and Exclude using Parameter Actions

Hello,

I was wondering if what I am trying to achieve can be done with the use of parameter actions. Below is a screenshot example and I can attach the sample workbook. I would like the report user to be able to click on a cell for a specific Week and for that cell to change color and be excluded from the Total shown. Ideally the user could select multiple weeks to be excluded. Is this possible?

• ###### 1. Re: Highlight and Exclude using Parameter Actions

Hi

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

Jim

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.

1 of 1 people found this helpful
• ###### 2. Re: Highlight and Exclude using Parameter Actions

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.

Weekly Sales

```// 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
```

Total Sales

```// 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

```// 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.

See attached.

1 of 1 people found this helpful
• ###### 3. Re: Highlight and Exclude using Parameter Actions

Jim,

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.

• ###### 4. Re: Highlight and Exclude using Parameter Actions

Ken,

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!

-Tommy

• ###### 5. Re: Highlight and Exclude using Parameter Actions

Great! This was a fun one!