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
Hello,
thanks for your answer,
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
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.
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
Ok, Thanks for the help 
