Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

!Creating a measure referencing other measures

Hi Team,    I'm trying to create a measure using an already created measure to calculate an allocation percentage. I have a list of child contracts that roll up into parent contracts (some have no ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous,

    #1, You can try to use the following formula to calculate active contract id.

    Active Percent =
CALCULATE (
    COUNT ( Table[Contract ID] ),
    FILTER ( ALLSELECTED ( Table ), [Status] = "Active" ),
    VALUES ( Table[Parent Contract] )
)

    #2, You can remove the allselected function to limit the calculation on current row content and invoke the above measure results.

    Allocation of Parent Percent =
DIVIDE (
    CALCULATE (
        COUNT ( Table[Contract ID] ),
        FILTER ( Table, [Status] = "Active" ),
        VALUES ( Table[Parent Contract] )
    ),
    [Active Percent],
    0
)

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
5 years ago

selimovd 

 

Source Tables: 

CRM Report = contains population of all child contracts

Parent Contract Pivot = listing of all active child contracts and associated parent contract

 

Measure for numerator: 

Count of Contracts = CALCULATE(COUNTROWS(DISTINCT('CRM Report'[CONTRACT_ID])))
^ should provide a count of 1 for each contract listing in a table. Having no issues with this.
 
Measure for denominator:
Active Child Count = DISTINCTCOUNT('Parent Contract Pivot__'[CONTRACT_ID])
^ applied this to a report table using parent contract as value & brought in measure to count # of child contracts for each parent with no issue. Having the issue when I apply this measure to a table with child contract ID as the value.
 
Let me know what else you may need to understand what I'm trying to do... thank you!!
 
selimovd's avatar
selimovd
Icon for Most Valuable Professional rankMost Valuable Professional
5 years ago

Anonymous 

 

I lost track what you want to do. Maybe I'm too stupid, but in every post the columns are called a little different. Once [count of contract], then [Active Child Count], then [Contract] and [Parent Contract].

 

Can you please post an easy-to-understand summary of your problem with a table with your Contract ID and all the measures and columns you are using?

 

By the way, COUNTROWS(DISTINCT and DISTINCTCOUNT should be the same for your last 2 measures