Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rich_P
Helper II
Helper II

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

ModHelpSoFar.png

 

 

I have a table of Goals by Client, by Year such as:

ModHelpGoals.png

 

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

ModHelpData.png

 

For now, I have settled on the relationship that looks like this:

ModHelpDiag.png

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!!!)

ModHelpResult.png

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

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

You may refer to my solution in this workbook.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

 

You may refer to my solution in this workbook.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:

ModHelpRespDiag1.png

 

And the result looks like this:

ModHelpResponse2.png

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
jmalone
Resolver III
Resolver III

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.

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.

ModHelpResponse1.png

 

Anybody have another approach?

 

Thanks,

Rich

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.