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.