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