10 Replies Latest reply on Oct 24, 2016 10:39 PM by Nikunj Bhardava

# How to use a formula to know if I choose all of one dimension?

I have two questions here --

Question 1

I have one dimension about region (A, B , C D)

How can I build a calculated filed to know if I choose all of them then measure A

If I only choose one of them -- then measure B

Question 2

Question B is due to my LOD for market share

There are region, segment, year data here -- ideally I want to build a dashboard in which market share can be calculated automatically when I choose different region, segment.

My current problem is when I filter to region.

The formula above hasn't taken region into consideration -- It works only for the total country level.

When I add 'region' into Fixed formula -- it works for each region separately but not for the total country level -- because some brands are not available in some regions -- then the fixed formula while deduct these regions for these brands

• ###### 1. Re: How to use a formula to know if I choose all of one dimension?

You should break this into two questions.

For the first half,

Create LOD:

{ FIXED : COUNTD(whatever dimension) }

This will give you the number of unique values in that dimension in your data source, regardless of filters, etc.

Then, within your sheet you can compare your COUNTD of filtered values with the overall COUNTD from the LOD.  If equal, then user has selected ALL.  If less, then they have filtered in fewer.  Choose A or B measure accordingly.

For the second half, I can't juggle all the descriptions.  I would need a hands-on sample workbook to play with.

• ###### 2. Re: How to use a formula to know if I choose all of one dimension?

Thanks, however I still got the problem -- I check my regional dimension -- there are 14 choices

So I use such:

IF COUNT([Regiont])=14 THEN 1

ELSE 2

END

But all the answer is 2....

• ###### 3. Re: How to use a formula to know if I choose all of one dimension?

For first question, I have attached demo.

Hope this helps

• ###### 4. Re: How to use a formula to know if I choose all of one dimension?

Baron -- Use COUNTD.  If you have multiple rows per region, COUNT will give you the count of rows, not the count of regions.

• ###### 5. Re: How to use a formula to know if I choose all of one dimension?

Baron -- Look at the example Nikunj did.  (Version 9.2 workbook).

[TotalRegions] does the LOD.

[SelectedRegions] does the COUNTD.  (Note:  COUNTD!)

[Selected Measure] does the type of comparison logic you will use to choose your measure.

• ###### 6. Re: How to use a formula to know if I choose all of one dimension?

Thanks for both of you!

• ###### 7. Re: How to use a formula to know if I choose all of one dimension?

May I have a follow up question -- I find one problem in my case is that not all the brands are available in all region -- e.g. Countd(Region) for Brand A is the number of total region

while the same measure for Brand B maybe 2 (because it doesn't have data in the other regions)

How can I change the measure regardless of whether the brands are national wide or regional wide?

• ###### 8. Re: How to use a formula to know if I choose all of one dimension?

Suddenly solve my issue -- because I either need to choose all of the region or only choose 1 of them -- then I turnaround the measure

when countd region = 1 or 0 ( mean there is no data for the brand) then Measure B

ese Measrue A

• ###### 9. Re: How to use a formula to know if I choose all of one dimension?

Thank You!

• ###### 10. Re: How to use a formula to know if I choose all of one dimension?

Thank You