Help with Simple Data Model

[Rich_P]

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.

Rich P

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution in this workbook.

Hope this helps.

[Rich_P]

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.:

Ashish rewrote the measures as - 

RR Goal Count:=if(
     		HASONEVALUE(Clients[Client]),
     			AVERAGE(qryCA_ManagementDashboard[RR Goal]),
     			SUMX(
     				SUMMARIZE(
     					VALUES(Clients[Client]),
     					[Client],"ABCD",
     					AVERAGE(qryCA_ManagementDashboard[RR Goal])
     					),
     			[ABCD])
     			)

RR Rcd Cnt :=
CALCULATE (
    COUNTROWS ( qryCA_ManagementDashboard ),
    qryCA_ManagementDashboard[Submittal Type] = "RR"
)

RR % Complete :=
DIVIDE ( [RR Rcd Cnt], [RR Goal Count] )

The relationships were defined as:

And the result looks like this:

[Ashish_Mathur]

You are welcome.

[jmalone]

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]

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