2 Replies Latest reply on Oct 11, 2018 5:58 PM by Michael Begg

    Help with Calculations

    Michael Begg

      Hello,

       

      I am trying to develop a dashboard for a Business Unit manager to track their results (Sales, GP etc) through a month and quickly answer the question "are we having a good/bad/average month". A key requirement is that the User can filter the dashboard to focus on a particular account (e.g Retail Sales) or clear the filter to see Total Sales.

       

      I am currently thinking of a line chart with the following:

      • Columns has MTD trading days ... ie 1 through to 23
      • 5 lines with different colours and possibly size
        1. Latest MTD result
        2. Latest year avg
        3. Latest year best monthly result
        4. Latest year worst monthly result
        5. Prior year avg
        • To achieve this, I think I need one Measure [Amount MTD] across one Dimension [Category] with the 5 members above. This way I can add [Amount MTD] as Rows and add [Category] to Colour mark card.

       

      I have attached a TWBX with 2 data sources and a start on the above:

      1. "Raw Data" is a sample of the raw data I have - daily amounts by account.
      2. "Data I want" is something I made up to simulate [Amount MTD] & [Category] from above.

       

      My questions:

      1. Do I need one Measure across one Dimension to achieve what I want? Or can I achieve what I want with 5 measures?
      2. How do I manipulate the data in Source 1 to be in the same format as Source 2.It seems like I need to create the five measures and then Pivot them, but that's not possible?? My thoughts are it would be much easier to do this in Tableau Prep/Alteryx HOWEVER...that causes problems when the User wants to see only one account .... the best month of the year for total sales and retail sales may not be the same month.

       

      Any ideas or help would be much appreciated?