Forum Discussion

Rich_P's avatar
Rich_P
Icon for Helper II rankHelper II
8 years ago
Solved

Help with Simple Data Model

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 th...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    You may refer to my solution in this workbook.

     

    Hope this helps.

     

jmalone's avatar
jmalone
Icon for Resolver III rankResolver III
8 years ago

It looks like you have 'RR Goal' column in both your Actual data and your ClientGoals tables. And from what I can tell, you don't need the ClientGoals table (maybe there is more to the story).

 

In this example, you can create a few simple measures on the "Actual" table (ie the qryCA_ManagementD.. table, which I will call Actual)

 

RR Goal Count := SUM('Actual'[RR Goal])
RR Record Count := COUNTROWS('Actual')
RR Pct Complete := DIVIDE( [RR Record Count] - [RR Goal Count] , [RR Goal Count] )

Those should give you the result you are looking for at the bottom of your post. Use the ClientID field in the Rows, and a Year from the Calendar table in the filters.

 

Maybe you have an additional need for both columns, but it looks like the single fact table ("Actual") will give you everything you need.

Rich_P's avatar
Rich_P
Icon for Helper II rankHelper II
8 years ago

First, thanks for the quick reply!

 

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.

 

Anybody have another approach?

 

Thanks,

Rich