4 Replies Latest reply on Nov 9, 2016 4:02 PM by John Quintana

Conditional Sum

Hi,

I need to group 3 cities total sales into 1 when a parameter value is selected.

EX:

I have 3 cities, Boston, Providence, and Springfield that all have sales

Boston - \$100

Providence - \$200

Springfield - \$50

I need to have the total - \$350 - to display when "Boston" is selected in the Parameter selector.  Parameter is pulled in from the Market field in the data.

I want to say IF [Parameter] = "Boston" THEN TOTAL SUM(Sales of all 3 cities)

Thank you!

• 1. Re: Conditional Sum

Hi, John. I think the answer in your case is pretty straightforward. To be clear, if Boston selected, you want the combined total for Boston, Providence and Springfield- but otherwise, what? In other words, what if Boston is not selected, what if the selection is Providence, or some other city? Thanks for advising.

• 2. Re: Conditional Sum

John - Assuming that if Boston is not selected then you just want the metric values for any/ all cities in your data.

If that's true, then the attached should give you one approach to solve for your use case with a parameter.

An alternate approach could be to use grouping on your [City] dimension, although that will make it impossible to individually select Providence or Springfield

• 3. Re: Conditional Sum

Hi Nick,

To answer your question, there are several cities and I just need the total for those 3 cities to display when Boston is selected, if Dallas for example is selected then I need the total sales for Dallas.

I received an error when trying to open that workbook. Not sure what happened.

Thank you!

• 4. Re: Conditional Sum

I tried to group the markets field to have Boston, Providence, and Springfield all together and Renamed the group to Boston. After I did that I cleared all parameters and then re-added from the Market filed in hopes that it would pick up the grouping, but with no luck.

Is there a way to group parameters?  Also, I have a filer set to true for Parameter=Market