Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Im having trouble calculating the following dataset due to not be able to certain functions in dimensions such as the sum() function.
This table is my dataset.
ProjectName | Funding | Program | Budget | Actual |
Project1 | 1 | A | 100 | 30 |
Project1 | 2 | A | 100 | 20 |
Project2 | 1 | A | 200 | 30 |
Project3 | 1 | B | 300 | 50 |
Project3 | 2 | C | 300 | 200 |
Project3 | 3 | C | 300 | 10 |
Project4 | 1 | A | 200 | 40 |
Project4 | 2 | A | 200 | 30 |
Project5 | 2 | A | 100 | 50 |
I want to be able to put the data in a bar chart using the dual() function to calculate the percentage of Actual against the Budget but I cant total the actual column due to being able to use the sum() function in the dimension. Is there an aggr() I could use or other function or load method to sum the Actual value for each specific project.
This is the dimension I want to use but cant. if(sum(Actual)/Budget > 1.5, Dual('150% & Greater',1).
The following code is where I am currently.
Dimension=if(Actual/Budget > 1.5, Dual('150% & Greater',1),
if(Actual/Budget > 1.25, Dual('125% - 150%',2),
if(Actual/Budget > .75, Dual('75% - 125%',3),
if(Actual/Budget >= .50, Dual('50% - 75%',4),
if(Actual/Budget < .50, Dual('Below 50%',5)
)))))
Measure = Count(Distinct ProjectName)
Try as calculated dimension
Aggr( if(sum(Actual)/Budget > 1.5, Dual('150% & Greater',1), ProjectName)
edit: replace the if() with whatever you need to classify your aggregated values
Try as calculated dimension
Aggr( if(sum(Actual)/Budget > 1.5, Dual('150% & Greater',1), ProjectName)
edit: replace the if() with whatever you need to classify your aggregated values