I have two sets of data
Training targets (list of courses and the number of seats per fiscal year)
Training Achievement (list of students that completed a course and which fiscal year it was)
I want to display the achievement as a % of the target in a pivot table and then use slicers to drill down.
I've put togther the attached example which only displays the correct % when I'm drilled down to the lowest level. I've obviously not doing the calculation the right way but I'm sort of lost at this point. Where am I going wrong?
Hi John
You need to do this in a DAX measure so that it can recalculate at the row level context based on the filtered position of the Slicers. If you do it in Power Query then you fix the level of granularity, which is why you're having problems.
In the attached file you'll see I've written a measure to do the calculation. Also take notice of the tables that the row and column fields came from in the PivotTable, as they are not the fact tables.
It looks like you haven't started the Power Pivot course yet, so I recommend you do that before moving forward with your own Power Pivot models.
Mynda
P.S. I've moved this thread to the Power Pivot thread.
Hi Mynda, Thanks for the response. I can't seem to download your attachment?
Hi John,
I've reattached the file, please try again.
Phil
Hi Phil, Thanks, got it now.
When I open the example file there is an error with the measure saying that COUNT can't work with a string?
Hi John,
I don't get that error, but you can just edit the measure and change the function from COUNT to COUNTA.
Mynda
Thanks Mynda, I worked this into my real data and it works perfectly.
