Calculate Percent of Actuals to Budget within one measure

[cnhodg]

Hi there, this is Chris using SAS VA (version 7.3 Hotfix 03). I'll start with the goal of what I want to accomplish in Visual Analytics, and next follow-up with more details. My goal is to show a gauge as a percent of currently received revenues to our budget, and similarly the same with expenses. Now to the details. Within the imported data, I have columns for 'account type', 'ledger', and 'amount', among others but I do not think the others are needed. Account type is either revenue or expense. Ledger is text as either 'Actuals 2018' or 'Budget 2018'. Amount is the only column with true, numerical data. That is, the amount column is always clarified by the other columns and so as an example, a $25,000 in the amount column could apply to either a revenue budget in 2017, a $25,000 expense in 2018, etc. Within VA I created a calculated item in the hopes to return percentages by doing what is displayed in the 'VA Calculated Item' attachment. The result is the bottom left graph in that image which shows 0 to 100%, but there is no black bar in the middle to show progress. If I change type from 'bullet' to 'thermometer' then the fill is blank. Any help is appreciated! Thanks, Chris

[cnhodg · Accepted solution]

Hey!

So truly, I have spent more than a day's worth of total hours on this. I finally figured it out literally 5 minutes ago. It turns out, for me I had to create two calculated items, then put those two items into an aggregated measure. Here's a bit more detail in case anyone else runs into this.

Originally, my data only had 'Amount' as a Measure. I then created calculated items for 'Actuals 2018 Revenue' and 'Budget 2018 Revenue' via simple If...Then statements because I had to qualify that within actuals and budget I only wanted account types like revenue. Lastly, I created an aggregated measure simply dividing the two calculated items. Voila!

I have no idea why it has to be like that, but apparently it does. Hopefully this helps for anyone else trying to do the same thing.

[AnnaBrown]

Great to hear you solved the problem, @cnhodg! Thanks for posting your solution back to the community, which for sure will help out fellow members.

Best,

Anna