Search code examples
tableau-apidimensionscalculation

Tableau calculated fields from 2 dimensions

I have a situation in Tableau where I need to do a calculation, but am stuck as to how best to approach it.

I have data in a table like this:

Organisation ID (Measure), Country (Dimension), FY16 Status (Dimension has 3 different values (Low,Medium,High)), FY17 Status (Dimension has 3 different values (Low,Medium,High)).

Sample of the Data: 12345, France, Low, Medium 12346, France, Medium, Medium 12347, France, High, Medium 12348, France, Low, High

What I need to do is create a table that shows the following columns:

  • Country (rows)
  • Count of Organisations per FY16 Low
  • Count of Organisations per FY17 Low
  • (Count of Orgs per FY17 Low - Count of Orgs per FY16 Low)/Count of Orgs per FY16 Low.
  • Count of Organisations per FY16 Medium
  • Count of Organisations per FY17 Medium
  • (Count of Orgs per FY17 Medium - Count of Orgs per FY16 Medium)/Count of Orgs per FY16 Medium.
  • Count of Organisations per FY16 High
  • Count of Organisations per FY17 High
  • (Count of Orgs per FY17 High - Count of Orgs per FY16 High)/Count of Orgs per FY16 High.

Any help would be greatly appreciated.

Please find the link to the Workbook here with a description of what exactly I am trying to achieve also in the Dashboard.

Solution

  • It's pretty simple.

    Create Low in FY16 as calculated field with formula

    IF [FY16 Status] = 'Low' THEN 1 ELSE 0 END

    Create Low/Medium/High in FY16/FY17Y in the same way

    Create Low Change as calculated field with formula

    (SUM([Low in FY17])-SUM([Low in FY16]))/SUM([Low in FY16])

    Create Medium Change and High Change as well

    Now you got all the columns it's time to create a sheet

    Sheet

    If you need I've uploaded this sheet as Tableau workbook with Excel data (link to be expired in 48 hours)