Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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])
)
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))
))
Hi Phalgun
The above will ignore any selections that are done.
Thanks but this will stop all filters from affecting the chart. I have other filters that need to work on this bar chart.
From my understanding, That is not possible because of the association.
Okay thanks for the quick response!
May be try ignoring selection in PO#, Vendor and sales because those are the underlying fields behind your calculated filters.
How can I do that? Could you please elaborate a little?
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])
)
Wow! This works like magic! Thanks a lot again!