3 Replies Latest reply on Mar 20, 2012 7:25 AM by Gregg Thomas

    Using common field across data sources in calculations

    Gregg Thomas

      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)/

      (case([Parameters].[Division Parameter])

      when 'Division 1' then (if ATTR([DL_Sales].[Division])='Division 1'then SUM([DL_Sales].[InstoreSalesCounts])end)end)

       

      Any suggestions?

       

      thanks!

      meg