Scenario
7:
Problem:
How to make % of Total Sales consistent after applying filter.
In
above image showing region wise percentage of total Sales. Before
applying filter you can see percentage of total sales for each
region. But when apply Region filter only for Central and West. The
percentage of total sales changed. This is not desired functionality.
I just want to filter rows not values.
You can
see my required output in image. How to do
this?
Solution:
we can
give solution in two ways.
1. using
lookup( ): This is not 100% perfect solution.
2. Using
Fixed( ): I am providing solution here using Fixed( )
Step
1: Create a calculated field as “Fixed
Sales” using below formula.
{FIXED
: sum([Sales])}
Step
2: Create a calculated field as “% of
Total Sales” using below formula
sum([Sales])/sum([Fixed
Sales])
Step
3: Drag Region to row
shelf and % of Total Sales to
Text shelf. Drag Region to
filter shelf and show it as quick filter. See below
Step
4: Right click on Agg(% of Total Sales),
go to format options, change number format to percentage.
Step
5: Now you will see sales in percentage. Change the filter and
see that percentage is not changing.
See Report Below: