I'm trying to figure out the best way to compare record counts of data in one table against a forecast number in another table. I have tried my best to implement all the various scenarios found in the books 'Definitive Guide to DAX', 'Power Pivot & Power BI,' 'Analyzing Data with MS Power BI and Power Pivot for Excel' as well as the "Budget" patterns found at the daxpatterns.com site. But for the life of me, I can't relate what I'm reading to what I NEED to do.
I think I know the concept at play here is one of Granularity. And it seems that this is exactly like a typical Budget pattern. But the closest i can get gives me some good results, but doesn't work int he Grand Total row at the bottom:
I have a table of Goals by Client, by Year such as:
And I have actual data from which I want to count the number of records and then compare that answer to the number in [RR Goal]:
For now, I have settled on the relationship that looks like this:
But I think I want to join the Goals table to the calendar table (it didn't work at all!).
Ultimately, I am looking for this result: (How simple is this!!!)
I have the Excel file available here if anyone wants to get me straightened out.
Much appreciated. Can't wait to see all the ways to do this.
As for the redundant fields, you're right, I'm sure I don't need them both. Originally, the RR and EA Goals were only in the Goals table. But one of the first things I tried was pulling them into the Management table because I couldn't decide how to join the Goals table.
I also tried going the route of building compound keys in each of the tables (see the GoalLink fields) so I could join them that way.
As the Goal table has Client AND Goal Year, I wasn't sure whether to try to join it to the Calendar table (hence the addition of the derived 'GoalStartDate' field) or to the Client field in the Management data. If I were doing this in a regular db Query, I would, of course, join the Actual and Goals tables on the Client +GoalYear fields.
I tried your suggestions, and they did not give me exactly what i was looking for. Though I did get the Grand Total correctly.
Ashish - Thank you very much. This is quite a solution. Absolutely NO WAY I would have arrived at this on my own. I need to digest it to understand your approach. For anyone following this, here are the highlights from his solution.: