4 Replies Latest reply on Feb 6, 2019 3:47 PM by bo.langvad

    Create measure that ignores filter

    bo.langvad

      From my ERP system I have a table as attached with actuals and budget entries. For the budget entries I have a dimension with the budgetversion. For the actuals the "version" is always NULL.

       

      I want to create two measures:

      - actual that shows all rows with "Type" = 'Actual' and ignores filtered values in "Version"

      - budget that shows all rows with "Type" = 'Budget'

       

      Based on these measures I want to calculate the deviation between actual and budget for the chosen "Version". I have inserted a table below with the wanted result when filtering on "Version" = 'B00'.

       

      I have attached an Excel spreadsheet with the table and a Tableau workbook where the filter doesn't work as I expect.

       

      -500

      AccountAccountNameActualBudgetDeviation
      1100Revenue-5.000-5.500
      2000Material2.0002.200200
      4000Salary1.0001.100100
      5000Interest1000-100
      Grand Total-1.900-2.200-300
        • 1. Re: Create measure that ignores filter
          Joe Oppelt

          You can use a FIXED LOD to grab that Actual amount, and it will go on all rows for the account, regardless of Type.

           

          In Sheet 2 of the attached, I made that calc.

           

          On Sheet 1(2) I added it to your measure shelf, and you can see that the Actual value is still there even though NULL has been filtered out.


          This works because a FIXED LOD gets evaluated before any filters are applied.

          1 of 1 people found this helpful
          • 2. Re: Create measure that ignores filter
            Michel Caissie

            Bo,

             

            For 'Actual', you better go with a FIXED lod.

            {FIXED [Account]: sum(IF [Type] = 'Actual' THEN [Amount] END)}

             

            As you can see on the sheet ValidateData, you get the value on every Row of the Account.

             

            Now if you want to show all the accounts in the view, even if you want to show only one Version  Budget value,  you cannot  put  Version on the filter shelf, because some Account would disappear of the view. For example, if you filter on B00, Account 5000 wont display because there is no rows with Version B00.

             

            But if you want to show it for a single Version, you can create a parameter -  String - Add from Field - Version

            and do the following calculation for the budget

            if [Version] = [Parameter 1] then [Amount] end

            This way you filter within the calculation  without removing any account from the view

             

            Michel

            1 of 1 people found this helpful
            • 3. Re: Create measure that ignores filter
              bo.langvad

              Thanks for your help. Also very usefull to use Sets to filter out NULL values.

              • 4. Re: Create measure that ignores filter
                bo.langvad

                Thanks for your help and a usefull workbook. I can see I should look into how to use the LOD's.