Skip to main content
Topics#Tableau Desktop & Web AuthoringTableau Community Forums Member (Inactive)'s Question
Tableau Community Forums Member (Inactive) (Salesforce) asked in #Tableau Desktop & Web Authoring
Jan 20, 2021, 9:24 AM

Slope calculation

Hi!

 

I have data points with the hierarchy of Function> Department > Team

Weeks (1-52) and KPI name (Quality or TAT)

with metrics (at the team level) met volumes and total volumes

 

Currently in the image attached the X-axis shows the performance (Variance =Actuals - Targets)

 

Y-axis - Slope of variation of actuals across the weeks (Actuals = Met vol/total vol)

 

the problem is Slope is calculated by =(N (Sxy) - (Sx * Sy) ) / (N (Sx^2)- (Sx)^2)

 

S is sigmod function or summation

N is number of weeks

x is Actuals

y is Weeks

Sx = sum of actuals

Sy = sum of weeks

Sxy Sum (Actuals*weeks)

 

now I'm able to calculate slope at team level as Actuals is calculated at row level (team level)

but how can workout the actuals for Departmenet because Actuals for Department is Sum of all met volumes in that department/ sum of all volumes in that department

 

this is creating a problem while calculating slope.

currently i have created 3 data sources at each hierarchy

I would like to use One data source at Team level

 

Can some one please help me out here

 

Thanks in advance

Nithin

cockpit.png
3 answers
  1. Tableau Community Forums Member (Inactive) (Salesforce)
    Jan 21, 2021, 9:01 AM

    Attached is the Workbook sample

    Calculated Fields

    Actuals = Met_vol/overall * I manually include Actuals in the data source need this to take from calculated field

    Variance = [Actuals]-[Targets] * I manually include Actuals in the data source need this to take from calculated field

    Targets for Departments - a= 90% B= 88% and C=96%

    Function Targets = 92% , 94%

    Performance = IF [Targets]>.95 and [Variance]>0.03 then 3

    ELSEIF [Targets]>.92 and [Variance]>0.04 then 3

    ELSEIF [Targets]=1 AND [Variance]=0 THEN 3

    ELSEIF [Variance]>0.05 then 3

    ELSEIF [Targets]>.999 AND [Actual_d]=1 THEN 3

    ELSEIF [Variance]<0 then 1

    ELSE 2

    END

     

    Sx = SUM([Weeks])

    Sy = SUM([Actuals])* I manually include Actuals in the data source need this to take from calculated field

    x2 = [Weeks]^2

    xy = [Weeks]*[Actuals]

    N = COUNT([Weeks])

    Numerator = ([N]*(SUM([xy])))-(([sx]*[sy]))

    Denominator = (([N]*(SUM([x2]))) - ([sx]^2))

    Slope = Numerator/Denominator

    Improvement = IF [slope]>0.0005 then 1

    ELSEIF [slope]<-0.0001 THEN 3

    ELSE 2

    END

    I started in Tableau from last year March, so I'm not so good in it let me know if there is some simple method

     

    Thanks,

    Nithin

    Tableau Cockpit test.xlsx
Write an answer...
0/9000