5 Replies Latest reply on Feb 25, 2016 12:55 AM by adrian.pellegrini

# How could I filter numerator and denominator of a ratio quite separately

Hi everyone,

I have a problematic with a database.

The question is : how could I filter numerator and denominator of a ratio quite separately?

Please find attached Excel file and workbook.

I want to get three views :

The one on the left is filtered by "Where" and "Produit"

The second one on the right must be filtered by "Produit" only

And the rate is the mix of both :

- Numerator : filtered by "Produit" only

- Denominator : filtered by "Where" and "Produit"

I have tried something (attached workbook) with duplicated datasources, but even when I link the two datasources only by "Produit", the where change the two fields.

I understand why it is not working but I do not have any solution at the first problematic.

Do you have a solution or idea ?

Many thanks,

• ###### 1. Re: How could I filter numerator and denominator of a ratio quite separately

You can actually use the same datasource for the views. For the product filter, apply that filter to all worksheets using the same source and for the where filter apply the filter to only the worksheet you need the values to change:

For product filter:

For the where filter:

So when you change the values in where only one sheet would get filtered. Does that work?

• ###### 2. Re: How could I filter numerator and denominator of a ratio quite separately

Or you can use LODs to achieve this:

Division: sum({ fixed [Produit] : sum([Quantity]) } )/ countd([Client ID])

This way COUNTD(Client ID) would respect both the where and produit filters and the numerator is fixed at a level of produit and hence will will ignore the where filter. For example if you select pineapple and paris the division will perform 24/1 for client B instead of 9/1. Hope that helps:

• ###### 3. Re: How could I filter numerator and denominator of a ratio quite separately

Hi Pooja,

Thanks a lot for answering my question.

Your solution about LOD expression using “fixed” is interesting, and we seem to be pretty close.

But when I keep filtering on the “Where”, removing the “null” values ; I still have an issue on the total : going from 92 to 85.

As you can see below:

ManyThanks !

• ###### 4. Re: How could I filter numerator and denominator of a ratio quite separately

So it looks like the cause of this is just bad data. In the image below, Peach display one row of with 2 nulls (where and client id). So when you take off the null from the filter, there is no other row to help retain that fixed value and hence 7 gets taken off the grand total. All other rows repeat the value for quantity because it is fixed at a level of produit. So when take off other members from the where filter the total of 92 persists. Hope that makes sense.

• ###### 5. Re: How could I filter numerator and denominator of a ratio quite separately

Hi Pooja,

Many thanks !

I think this point is exactly my problem.

A you might expect, this is an example dataset and on the real dataset I have much more than one occurence of this kind of lines.

That said, would you think I could find any way to resolve that issue ?