Cost Recovery

[Bernie6808]

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 department belongs to a faculty. All of this data in contained within each budget line. My X Axis will always show 'Cost Recovery' which is calculated as 'Price' / 'Cost' (with an 'If' statement to avoid #DIV/0 errors)

I can group the budget lines table by Faculty/year, Department/year or Funder/year in the query editor, but I then lose the ability for users to drill into a data point because the data is grouped before it gets into the chart:

Can anyone point me to a way to show the data sliced in any way without grouping first, to allow users to drill down?

[Bernie6808]

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

Here's an example of my data:

Project_ID	Award_Year	Department_Code	Funder_Category	Cost	Price	Cost_Recovery
843698	2021	PHSS	Councils	37093.11	37093.11	100%
843698	2022	EES	Councils	3008.63	0	0%
843698	2022	PHSS	Councils	40161.84	0	0%
843698	2023	PHSS	Councils	168.24	0	0%
843698	2023	PHSS	Councils	9455.87	0	0%
843698	2023	PHSS	Councils	4549.58	0	0%
843698	2023	PHSS	Industry & Commerce	7140	7140	100%
843698	2023	PHSS	Industry & Commerce	3600	3600	100%
843698	2023	PHSS	Industry & Commerce	600	600	100%
843698	2023	PHSS	Industry & Commerce	3600	3600	100%
843953	2023	HCS	Government Departments	21418.91	10854.77	51%
843953	2023	HCS	Government Departments	8259.78	4823.76	58%
843953	2023	HCS	Government Departments	66.93	16.83	25%
843953	2023	HCS	Government Departments	1760.18	443.01	25%
844269	2023	BIO	Councils	2000	2000	100%
844269	2023	BIO	Councils	1000	1000	100%
844269	2023	BIO	Councils	26389.65	27346	104%
844269	2023	BIO	Councils	3917.52	0	0%
844269	2023	BIO	Councils	29749.51	0	0%
844269	2023	BIO	Councils	24.92	0	0%

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.

[Ritaf1983 · Accepted solution]

Hi @Bernie6808 

If I understood you correctly and you want to give users the option to choose by wich category they want to group the measure you can use field parameters.

https://www.youtube.com/watch?v=5vLBnrPyPgc

if it is something else 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly