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
8 years ago
Hi,

You may refer to my solution in this workbook.

Hope this helps.

Rich_P

Helper II

8 years ago

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