4 Replies Latest reply on Feb 4, 2019 6:52 AM by Jim Dehner

    fixed calculation when filtering

    Alysse Mea

      I have this view below:

      When I select a Department, I should still be able to show all measures I have in my view. Sum values remain the same but others will change. For example: if Department filter = PTO, Access value for 2/3/2018 for Alabama =156. Access/Sum = 156/1995259 . I can't make my FIXED calculation work. Please help! Thank you. I attached my workbook 10.5

        • 1. Re: fixed calculation when filtering
          Mahfooj Khan

          Hi,

           

          Find my approach in attached workbook. I've duplicated your existing calculated fields and modified the logic with FIXED{} expressions. Follow the same approach for other measures.

          Let us know if this help.

           

          Mahfooj

          • 2. Re: fixed calculation when filtering
            Jim Dehner

            Good morning

             

            in words this formula will create a level in the data set at the location and date level  that will sum the quantity based on your condition for each department

             

             

            you can then use it as denominator in your other calculations and will return this

             

             

            I have a question for you - are you stuck with the format on the input file - the reason I ask is that you are going through a lot of work to separate out the sum - business - and access - in the data - you would be better served if those values were "Un-pivoted" in the source data -

             

            I noticed you also have a dimension DepatrmentII - which you may need to add into the Fixed expression just like th Department

             

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            • 3. Re: fixed calculation when filtering
              Alysse Mea

              Jim, my source data is 'pivoted' to limit my number of records. Can you please attach your workbook? Any version will do. I have 2018.3. Thanks

              • 4. Re: fixed calculation when filtering
                Jim Dehner

                OK it will just mean more work at the viz level - a calculation for each metric

                I could not remember which version you are on so I sent 2

                Jim

                If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.