you can create calculated field = actual/planed. and change this calculated field format to percenteage.
Can you make this calculated field?
SUM([actual spending]) / SUM([planned spending])
Then whatever dimension you use this % will be accurate. You can create it to show always as a percentage by doing the following.
1) Create the calculated field as above
2) Right click the measure in the measure pane
3) Choose default properties
4) Go down to number format
5) Choose percentage
Hi! Unfortunately that doesn't work, because the actual spent amount is only in one column, and tableau won't let me differentiate between actual amount spent and planned amount. I've tried creating sets and doing calculations through those, but that doesn't seem to work either, because the sets use boolean data!
You would need to create a Blend with the same Data set i.e. you will need to duplicate the data source then go to "Edit Relationship" and define the condition . There after you will be able to achieve what you are trying to .Try this out and let me know if it works.
1 of 1 people found this helpful
I misread your issue. Sorry I was mobile.
Jusran was the closest here. Try this calc
SUM(IF [Type] = 'Actual Spending' THEN [Value] ELSE 0 END)
SUM(IF [Type] = 'Planned Spending' THEN [Value] ELSE 0 END)
This will look through every row and if the type is actual spending it returns the value else it is 0 and then it sums that up and divides it by the same but for planned spending.
Then for each field it will give you the correct %. For instance if your data had another column that said Region as a header and the members would be something like USA, USA, EMEA, EMEA. So when you put my above calc in the view and this region field in the view as well you will get the resultant % for both regions.
Past this you will need to share sample data or be entirely more descriptive of your data structure and of your desired output.