Forum Discussion
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 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?
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/1447523If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
2 Replies
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/1447523If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
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.
