This content has been marked as final. Show 3 replies
i'm struggling with filtering a view based on a companies divisions. I have 2 data sources blended, both of which have a "divisions" column. I have a calculation that takes a sales amount from 1 data source and divides it by the total counts from a separate data source. Both of these need to be filtered by division. So if I select Division 1, then both the sales amount and the counts should be filtered for only Division 1.
I can't get the denominator to change based on the division that I've selected. I've tried all of the following;
Action Filters on a dashboard - using a separate worksheet of "division" names as the filter...this only affects the numerator, leaving the entire companies counts as the denominator.
Creating a parameter for divisions, then writing a calculated field as follows - this doesn't produce anything:
(case ([Parameters].[Division Parameter])
when 'Division 1' then (if ATTR([Division]) = 'Division 1' then sum([SalesAmt])end)end)/
when 'Division 1' then (if ATTR([DL_Sales].[Division])='Division 1'then SUM([DL_Sales].[InstoreSalesCounts])end)end)