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.

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi,

 

You may refer to my solution in this workbook.

 

Hope this helps.

 

  • Rich_P's avatar
    Rich_P
    Icon for Helper II rankHelper 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] )

     

    The relationships were defined as:

     

    And the result looks like this:

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      8 years ago

      You are welcome.