3 Replies Latest reply on Jul 31, 2016 9:20 AM by Tom W

    "Total" calculation multiply itself

    Matan Bracha

      Hi all, I'm in a big mess here.

       

      I've published this a few days ago:

      I have a crosstab, and I'm mixing in it data from 2 data sources.

      My dimensions are from source A, and I have a few measures that are calculations and the data comes from source B.

       

      Example:

      CASE ATTR([A].[Val])

      WHEN "abc" THEN ZN(SUM([B].[abc_Clicks]))

      WHEN "def" THEN ZN(SUM([B].[def_Clicks]))

      WHEN "ghi" THEN ZN(SUM([B].[ghi_Clicks]))

      ELSE 0

      END

       

      On the crosstab I can see the different values according to "Val", but in the "Total" row I have 0:

       

      ---------------------------------------------------------------------------------------------------------------------------

       

      The answer I got is to use this calculation:

      IF ATTR([A].[Val]) = ATTR([A].[Val])

      THEN

      CASE ATTR([A].[Val])

      WHEN "abc" THEN ZN(SUM([B].[abc_Clicks]))

      WHEN "def" THEN ZN(SUM([B].[def_Clicks]))

      WHEN "ghi" THEN ZN(SUM([B].[ghi_Clicks]))

      ELSE 0

      END

      ELSE ELSE ZN(SUM([B].[abc_Clicks])) +  ZN(SUM([B].[def_Clicks])) + ZN(SUM([B].[ghi_Clicks]))

      END

       

      So I tried it - and it showed me a "Total" value - only that my values multiply themselves in the number of rows in the dimension...

      Example:

       

      I have no idea why the calculation is so messed up, or why it didn't work in the first place...

      But I'm really getting frustrated about this, I'm on this for days and I can't find a solution...

       

      So - If anyone has any idea how to solve my original problem (a different solution, or something to fix the calculation that I was already given) that will mean a lot to me.

      Thank you very much in advance!!!

        • 1. Re: "Total" calculation multiply itself
          Tom W

          Hi Matan,

          So firstly, you shouldn't need to create a new post for this. It makes sense to continue on in your existing discussion, especially given that others have already started contributing to it. It helps newcomers to the thread understand what you've already tried, and we don't end up with several threads for essentially what will be the same root cause.

           

          With regards to your problem, it's going to be really tough for us to provide insight here without seeing a Tableau Packaged Workbook including sample data. I'm sure you're hesitant to do that, so I'd suggest you mock up some sample data in excel and re-create the problem with sample data so we can take a look at that.

           

          Thanks!

          1 of 1 people found this helpful
          • 2. Re: "Total" calculation multiply itself
            Matan Bracha

            You're right, I'm attaching a small sample from my data - the tables has the main Dimension and attributes that I use in the views...

            There are three sheets in the workbook:

            - One called "Master Report" - in which the calculation works.

            - The second called "Case" , It has the cross-tab for only "Case" calculation ---> "Total" shows zero

            - The third called "If", it has the cross-tab for the "If" calculation as showen up ---> "Total" isn't calculated correctly, it shows the "Total"* # of rows in the "Source" dimension"

             

             

            Hopefully this would help, Thank you!

            • 3. Re: "Total" calculation multiply itself
              Tom W

              On your if sheet, you could change the ClicksIf formula to;

              IF ATTR([Source])=ATTR([Source]) THEN

                  CASE ATTR([Source])

                      WHEN "A" THEN ZN(SUM([WithoutCampaign (Sample)].[A Clicks]))

                      WHEN "B" THEN ZN(SUM([WithoutCampaign (Sample)].[B Clicks]))

                      WHEN "C" THEN ZN(SUM([WithoutCampaign (Sample)].[C Clicks]))

                      ELSE 0

                  END

              ELSE  (ZN(SUM([WithoutCampaign (Sample)].[A Clicks]))

              +ZN(SUM([WithoutCampaign (Sample)].[B Clicks]))

              +ZN(SUM([WithoutCampaign (Sample)].[C Clicks])))

              /COUNT([WithoutCampaign (Sample)].[Date])

              END

              Because you're joining, two sources, you are effectively duplicating the metrics and thus you need to divide by something. This will give you the correct totals, but your grand total is going to be off.

               

              Here's a rather long thread, but one which you should read - Re: Grand Total doesn't work!