9 Replies Latest reply: May 24, 2017 4:44 AM by Phalgun Parvathaneni

# Prevent a Filter from affecting a chart

Hello all! I have a bar chart with calculated measure as:

Sum({<[Sales]={"<=\$(=\$(vInput))"},[Type]={'N'}>} Aggr(

If( Rangesum(Above(Sum({<[Sales]={"<=\$(=\$(vInput))"},[Type]={'N'} >} [Sales])/Sum({<[Sales]={"<=\$(=\$(vInput))"},[Type]={'N'} >} total [Sales]),1,RowNo()))<=0.8,

Sum({<[Sales]={"<=\$(=\$(vInput))"},[Type]={'N'} >}[Sales])

)

,

([Vendor],(=Sum({<[Sales]={"<=\$(=\$(vInput))"},[Type]={'N'} >}[Sales]),Desc))

))

Now I also have 3 filters which are calculated as follows:

1.Bucket by PO:

Aggr(If(Sum({1}{<[Type]={'N'}>}[Sales]) < 5000, Dual('5k or Below',1),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 50000, Dual('5k-50k',2),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 100000, Dual('50k-100k',3),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 250000, Dual('100k-250k',4),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 500000, Dual('250k-500k',5),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 1000000, Dual('500k-1Mn',6),

If(Sum({1}{<[[Type]={'N'}>}[Sales]) < 10000000, Dual('1Mn-10Mn',7),

Dual('>10Mn',8)))))))), [PO#])

2.Bucket by Vendor:

Aggr(If(Sum({1}{<[Type]={'N'}>}[Sales]) < 5000, Dual('5k or Below',1),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 50000, Dual('5k-50k',2),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 100000, Dual('50k-100k',3),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 250000, Dual('100k-250k',4),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 500000, Dual('250k-500k',5),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 1000000, Dual('500k-1Mn',6),

If(Sum({1}{<[[Type]={'N'}>}[Sales]) < 10000000, Dual('1Mn-10Mn',7),

Dual('>10Mn',8)))))))), [Vendor])

3.Vendor pareto:

Aggr(

If(Rangesum(Above(Sum({<[Vendor]>} [Sales])/Sum({<[Vendor]>} TOTAL [Sales]),0,RowNo()))<=0.8, Dual('Top 80%', 1),

If(Rangesum(Above(Sum({<[Vendor]>} [Sales])/Sum({<[Vendor]>} total [Sales]),0,RowNo()))<=0.95, Dual('80-95%', 2),

Dual('Bottom 5%', 3))),

([Vendor],(=Sum({<[Vendor]>} [Sales]),Desc))

)

Now I don't want these 3 filters to affect my bar chart. If any selections are made on these filters, the bar chart should not change. Is there any way to achieve this? TIA!

• ###### Re: Prevent a Filter from affecting a chart

Sum({1<[Sales]={"<=\$(=\$(vInput))"},[Type]={'N'}>} Aggr(

If( Rangesum(Above(Sum({<[Sales]={"<=\$(=\$(vInput))"},[Type]={'N'} >} [Sales])/Sum({<[Sales]={"<=\$(=\$(vInput))"},[Type]={'N'} >} total [Sales]),1,RowNo()))<=0.8,

Sum({<[Sales]={"<=\$(=\$(vInput))"},[Type]={'N'} >}[Sales])

)

,

([Vendor],(=Sum({<[Sales]={"<=\$(=\$(vInput))"},[Type]={'N'} >}[Sales]),Desc))

))

• ###### Re: Prevent a Filter from affecting a chart

Thanks but this will stop all filters from affecting the chart. I have other filters that need to work on this bar chart.

• ###### Re: Prevent a Filter from affecting a chart

Hi Phalgun

The above will ignore any selections that are done.

• ###### Re: Prevent a Filter from affecting a chart

From my understanding, That is not possible because of the association.

• ###### Re: Prevent a Filter from affecting a chart

Okay thanks for the quick response!

• ###### Re: Prevent a Filter from affecting a chart

May be try ignoring selection in PO#, Vendor and sales because those are the underlying fields behind your calculated filters.

• ###### Re: Prevent a Filter from affecting a chart

How can I do that? Could you please elaborate a little?

• ###### Re: Prevent a Filter from affecting a chart

Hahaha basic set analysis

Sum({<[Sales]={"<=\$(=\$(vInput))"},[Type]={'N'}, Vendor, PO#, Sales>} Aggr(

If( Rangesum(Above(Sum({<[Sales]={"<=\$(=\$(vInput))"},[Type]={'N'}, Vendor, PO#, Sales>} [Sales])/Sum({<[Sales]={"<=\$(=\$(vInput))"},[Type]={'N'}, Vendor, PO#, Sales>} total [Sales]),1,RowNo()))<=0.8,

Sum({<[Sales]={"<=\$(=\$(vInput))"},[Type]={'N'}, Vendor, PO#, Sales>}[Sales])

)

• ###### Re: Prevent a Filter from affecting a chart

Wow! This works like magic! Thanks a lot again!