# How to mix filters and change partition level in the same calculation?

Hello,
In a calculation, I’d like to use at the same time a filter (like I do with functions such as avgif)
AND modify the partition of the calculation (like I do with LAC function, LAC-A with avg or LAC-W with avgover).

Is it possible to do it at the same time ? (I manage to do each but not mix it).
PS : I need to do it in a calculation, I can’t use a filter from the Panel filter (because not every part of the calculation will use the same filter).

Thanks

Hi @bherard ,

Can you provide an example with sample data on what you are trying to achieve and the expected output ?

Kind regards,
Koushik

1 Like

Hello,
in fact, I’d like to calculate something like this :

100 * (sum({value}) / sum({value “of a specific value of the dimension, the value is selected by user”}))
(We called it a base-100 calculation).

To give you an example, suppose we have following data :
Brand;Value
A;500
B; 250
C;600

the aim is to calculate, if user select brand A :
A; 100
B; 50
C; 120

if user select Brand B :
A;200
B;100
C;240

etc …

the numerator is OK, difficulty is on denominator, I tried a lot of things (I can detail it, if needed) but I don’t manage to do it.

Do you have any idea ?

Regards

Hi @bherard ,

I understand this is the sample data

To give you an example, suppose we have following data :
Brand;Value
A;500
B; 250
C;600

So if you have filters on brands and user selects brand A : How are the below values changing to (100 , 50 and 20 ) ?

A; 100
B; 50
C; 120

1 Like

Data set :
Brand;Value
A;500
B;250
C;600

the aim is to calculate :
If user select brand A :
numerator = Sum (value)
denominator = Sum (value) for Brand A
Brand ; Value ; Sum(value) ; Sum (value) for Brand A ; Base 100 = (100*Sum(value)/ Sum (value) for Brand A)
A;500;500;500;500/500 * 100 = 100
B;250;250;500;250/500 * 100 = 50
C;600;600;500;600/500 * 100 = 120

if user select Brand B :
Brand ; Value ; Sum(value) ; Sum (value) for Brand B ; Base 100 = (100*Sum(value)/ Sum (value) for Brand B)
A;500;500;250;500/250 * 100 = 200
B;250;250;250;250/250 * 100 = 100
C;600;600;250;600/250 * 100 = 240

The point is to apply different filters on the numerator and on the denominator,
(Numerator => Filter to “Brand name of the dimension”, Denominator => Filter to Reference Brand).
so it needs to be done in calculation, but I’m fight to mix filters (with ifelse or sumif etc …) with agg functions.

@Koushik_Muthanna I’ve edited my previous posts to add some details
regards

1 Like

Hi @bherard ,

I have looked into this and what you are trying to achieve is to have the selected brand value be replicated for every row so that calculations can be performed.

Using SPICE
The one possible method is by using parameters , it’s not neat solution as you might have to add some guidance to end-users when using the dashboard particularly when using the controls.

1/Create a parameter called brand and then create a control . Set default value ‘Brand A’
2/Create a parameter called value and then create a control. Set default value ‘0’
3/Show relevant values for parameter value and link it to brand

Now when you select for eg : Brand A , it shows the corresponding value.
Create a calculated field > brandvaluespice and pass the paramater : value
Create another calculated field > base-100-calculation: 100*sum(value)/sum(brandvaluespice)

Note : When you change to a different brand , the initial parameter value selected would still remain.

Selected new Brand B

Click on the value for it to reflect only available values for Brand B

Using Direct Query : Custom SQL

With latest release of being able to use parameters , you can pass the specific value at query execution

1/In SQL : main is the dummy data
2/brand is using parameters to get the value ( \$pbrand default is ‘Brand A’)
The final join will replicate the value selected for the specific brand on all rows

``````WITH main AS ( select 500 as value, 'Brand A' as Brand , 1 as joinc
union all
select 200 as value, 'Brand B' as Brand , 1 as joinc
union all
select 600 as value, 'Brand C' as Brand , 1 as joinc )

, brand AS (select value as value_brand ,1 as joinb
from main
where Brand =  <<\$pbrand>> )

select m.*,b.* from main m join brand b on m.joinc=b.joinb
``````

QuickSight you can map the parameter for passing values

brand is linked to the dataset

base-100 calculation `100*sum(value)/sum({value_brand})`

Kind regards,
Koushik

2 Likes

Ok, Thanks for the help

1 Like