Forum Discussion

Bernie6808's avatar
Bernie6808
Frequent Visitor
2 years ago
Solved

Cost Recovery

I have a large set of data comprised of budget lines, each with a 'Award_Year' 'Cost' and 'Price'. Each budget line belongs to a project, which belongs to one or more funders and departments. Each de...
Need Help
Bernie6808's avatar
Bernie6808
Frequent Visitor
2 years ago

Thanks Ritaf1983 and sorry if I didn't provide enough information.

 

Here's an example of my data:

 

Project_IDAward_YearDepartment_CodeFunder_CategoryCostPriceCost_Recovery
8436982021PHSSCouncils37093.1137093.11100%
8436982022EESCouncils3008.6300%
8436982022PHSSCouncils40161.8400%
8436982023PHSSCouncils168.2400%
8436982023PHSSCouncils9455.8700%
8436982023PHSSCouncils4549.5800%
8436982023PHSSIndustry & Commerce71407140100%
8436982023PHSSIndustry & Commerce36003600100%
8436982023PHSSIndustry & Commerce600600100%
8436982023PHSSIndustry & Commerce36003600100%
8439532023HCSGovernment Departments21418.9110854.7751%
8439532023HCSGovernment Departments8259.784823.7658%
8439532023HCSGovernment Departments66.9316.8325%
8439532023HCSGovernment Departments1760.18443.0125%
8442692023BIOCouncils20002000100%
8442692023BIOCouncils10001000100%
8442692023BIOCouncils26389.6527346104%
8442692023BIOCouncils3917.5200%
8442692023BIOCouncils29749.5100%
8442692023BIOCouncils24.9200%

 

Cost_Recovery will always be on the x-axis of my chart. Users will want to filter or drill into the data using any combination of fields, but because Cost-Recovery is a percentage it would need to be calculated 'on the fly' wth each dissection of the data; it can't be expressed as sum or percentage of the ungrouped data above.

 

I know how to group the data in the query editor, but by doing this I lose the ability for users to drill into the data.