4 Replies Latest reply on Jun 6, 2016 10:56 PM by Raja Saab

# Calculation Help

Hello,

So i am creating a Dashboard type Report that lists the KPIs by Type (Actuals, Budget) horizontally. What i want to do is

1. Add a row at the bottom that calculates the % to budget for the Actuals

2. Once i do that i want to color the Actual number cell accordingly - RED is it is below budget, GREEN if above etc.

I am attaching here a screenshot of my worksheet that shows the dimensions and the measures i have

Definition (incase it is not clear from the screenshot)

KPI - it is the overall KPI (eg in this case Total Members)

Value Type - is the type of the KPI value (eg. Actual or Budget)

Week_of_Year - are the weeks of the year

Value - is the measure (eg.      For Week 1 - 274,808 Actual and 273,724 Budget)

I want to add a row at the bottom that shows the % to budget values(for the example) - for Week One it should show 100.396% ((274808/273724) * 100))

AND i want to color the Cell of the Actual Value (274808) GREEN.

Any help is much appreaciated!!!

• ###### 1. Re: Calculation Help

Is that what you wanted?

If actuals > budget then actuals green else budget red vice verse. I've done some hack to get the percentage because your actual and budget both are in same columns.

Workbook has been published in my public profile. Have a look.

Feel free to ask If you've any question.

Mahfooj

1 of 1 people found this helpful
• ###### 2. Re: Calculation Help

Mahfooj,

Many Many thanks for the response. I am currently traveling reaching my destination quite late in the night. I saw the workbook and looks like this is exactly what i wanted to achieve. I will download and check it out to my Tableau environment and let you know incase i have questions!

Once again - many thanks

- Vivek (Raja)

• ###### 3. Re: Calculation Help

Hi Mahfooj,

you are searching up for the max value from first and checking with max value from last    row  ,why you are evaluating to 1

and also can you share xls sheet ?

IF ATTR([Value Type])='Actuals' AND

LOOKUP(MAX([Value]),FIRST())>LOOKUP(MAX([Value]),LAST())

THEN 1

• ###### 4. Re: Calculation Help

If you see the last line of requester's requirement where requester wants

to color the actual If its above budget. Integer is much faster then string

in calculations. So make it more efficient I've used integer. I hope you

get my point.