cancel
Showing results for 
Search instead for 
Did you mean: 

Calculate and compare selected member to non-selected members

zohebakber
9 - Travel Pro
9 - Travel Pro
Calculate and compare selected member to non-selected members

Hello,

In the screenshot below, I want one bar for the selected dashboard filter and the purple bar for the rest(non selected) aggregated value.
Can you please help me here.

 

@Ido_QBeeQ @harikm007 @intapiuser @Michal 

Thank you

zohebakber_0-1693215520625.png

 

 

2 ACCEPTED SOLUTIONS

wallingfordce
10 - ETL
10 - ETL

It's not pretty, but you could do a CASE statement if the list of countries isn't changing too much...

CASE
WHEN [Total of Something] = ( [Total of Something] , [Country = India] )
THEN ( [Total of Something] , [Country <> India] )
WHEN [Total of Something] = ( [Total of Something] , [Country = US] )
THEN ( [Total of Something] , [Country <> US] )
ETC. END

Or if you have both a Country field and a Country ID field, and the Country description is on Categories, then you can get more exacting (the above could throw bad results if 2 countries have the same [Total of Something].

CASE WHEN [Max Country ID] = {hard-coded Country ID of India}
THEN ( [Total of Something] , [Country <> India] )
ETC. END

Otherwise, I'd be eager to hear of others' tricks for getting a formula filter to more dynamically adjust in response to the category grouping or higher-level filter changes.

View solution in original post

irismaessen
11 - Data Pipeline
11 - Data Pipeline

If it's totals or counts you're calculating (this doesn't work with averages unless you calculate them yourself from counts/sums), I wonder if you can't have two formulas, one of which is 
Formula 1: [Total of Something] 
Formula 2: ([Total of Something], ALL(Country)) - [Total of Something]


You can still combine this with filters on region/country on the dashboard itself by duplicating the 'Country' column and using one column in the dashboard filter and one in the widget
If you have no filter on Country, Formula 2 will be 0, but if there *is* a filter on Country, you should see values for Formula 1 *and* Formula 2

View solution in original post

4 REPLIES 4

Ido_QBeeQ
9 - Travel Pro
9 - Travel Pro

Hey @zohebakber 

I believe you can achieve this by using the ALL() function, use the ALL () funtion on the mesure that you want not to be affected by the filter, see the link below:

https://docs.sisense.com/main/SisenseLinux/creating-formulas-based-on-criteria-and-conditions-filter...

 

zohebakber
9 - Travel Pro
9 - Travel Pro

Hi Ido,

Thanks for replying.
Actually All function won't help here.
Let's say I have a region (India, US, UK) filter on the dashboard.
User selects India as a filter, I want my pink bar to populate with region = India and purple bar to populate region = US and UK.
So, that, we can compare the selected value and unselected value.

Thanks

wallingfordce
10 - ETL
10 - ETL

It's not pretty, but you could do a CASE statement if the list of countries isn't changing too much...

CASE
WHEN [Total of Something] = ( [Total of Something] , [Country = India] )
THEN ( [Total of Something] , [Country <> India] )
WHEN [Total of Something] = ( [Total of Something] , [Country = US] )
THEN ( [Total of Something] , [Country <> US] )
ETC. END

Or if you have both a Country field and a Country ID field, and the Country description is on Categories, then you can get more exacting (the above could throw bad results if 2 countries have the same [Total of Something].

CASE WHEN [Max Country ID] = {hard-coded Country ID of India}
THEN ( [Total of Something] , [Country <> India] )
ETC. END

Otherwise, I'd be eager to hear of others' tricks for getting a formula filter to more dynamically adjust in response to the category grouping or higher-level filter changes.

irismaessen
11 - Data Pipeline
11 - Data Pipeline

If it's totals or counts you're calculating (this doesn't work with averages unless you calculate them yourself from counts/sums), I wonder if you can't have two formulas, one of which is 
Formula 1: [Total of Something] 
Formula 2: ([Total of Something], ALL(Country)) - [Total of Something]


You can still combine this with filters on region/country on the dashboard itself by duplicating the 'Country' column and using one column in the dashboard filter and one in the widget
If you have no filter on Country, Formula 2 will be 0, but if there *is* a filter on Country, you should see values for Formula 1 *and* Formula 2