CODEWITHSUNDEEP
tableau-api
Sargam Gupta
Sargam Gupta

Reputation: 3

Calculating % Complete

enter image description here

From the Image I need to calculate % complete for the order number 1.

Calculation for complete will be

(total number of points for status 'Delivered' / total number of points)*100

The value should be 57%

Can some one help with the calculated field?

Upvotes: 0

Views: 106

Answers (2)

Alex Blakemore
Alex Blakemore

Reputation: 11921

You can also get the effect you want, just by manipulating the Tableau user interface -- without needed to write calculated fields as @S. User18 showed. Both approaches work, but it helps to understand both alternatives.

As with @S. User18, I assume your column named Order No. is consistently filled in. I also assume [Order No.] is a dimension.

  1. Place [Order No.] on a shelf, say Rows
  2. Place [Status] on another shelf, say Columns
  3. Place Sum([Point]) on a shelf, say Text

This shows the sum of the number of Points for each combination of the two dimensions: [Order No.] and [Status]

  1. To convert the number of Points to a percentage, right click on Sum([Point]) on the marks card, and choose Quick Table calculation->Percent of Total
  2. Similarly, right click on Sum([Point]) and experiment with different "Compute Using" settings to get the effect you want -- i.e. percent of each row, column, table etc. You can instead Edit the Table Calc if the preset Compute Using options don't get the effect you want.
  3. Right click on any row or column headers that you want to hide - so if you only want to display the percentages for the Completed Status, hide the others. Don't exclude the others as that will change the calculations.

To understand more about Table Calculations, see the online help.

Upvotes: 0

S. User18
S. User18

Reputation: 712

Look at IIF in help files

SUM(IIF([Status]="Delivered",[Point],NULL)) / SUM([Point])

I assume your column named Order No. extends all the way down each row (i.e., each row should have a 1 as Order No. but the screenshot does not show that.

Also, the above only works if the status of "Delivered" is consistently cased, which it is not in the screenshot. If it is not consistently cased, wrap [Status] in an Upper function:

SUM(IIF(UPPER([Status])="DELIVERED",[Point],NULL)) / SUM([Point])

Upvotes: 1

Related Questions