Solved: !Creating a measure referencing other measures

Anonymous · ‎04-21-2021

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 parent contract) and I need to identify the allocation that each child contract has in relation to the parent.

I created a measure to identify the denominator in the function ("Active Child Count") using the DISTINCTCOUNT formula. However, now I'm struggling to create the second measure of the allocation-- I tried using DIVIDE([count of contract],[Active Child Count],1) but this is showing me that all contracts have a 100% allocation, which isn't accurate.

Below is the table I'm expecting to see based on sample contracts/data:

Contract ID	Parent Contract	Active Child Count	% Allocation
123456	A12230	2	50%
789101	A12230	2	50%
123412	B16678	1	100%
123433	-	0	100%

Still a new power BI user so I apologize if I've botched any of the terminology, but greatly appreciate any guidance here.

Thanks!

Anonymous · ‎04-26-2021

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

View solution in original post

Anonymous · ‎04-21-2021

@selimovd meant to tag you in the above

selimovd · ‎04-21-2021

@Anonymous can you show us the measure?

Anonymous · ‎04-21-2021

@selimovd

Sorry, let me start over...

I have the following columns in my data source:

Contract ID	Parent Contract	Status
123456	A12230	Active
789101	A12230	Active
827381	A12230	Active
647325	A12230	Terminated
123412	B16678	Active
123433	-	Active

I'm trying to create two measures to be used in a report table that lists all Contract IDs with the associated measures:

Measure #1: Count all active child contracts for each parent contract. If I did this in excel, I'd simply pivot the data to show each parent contract and count the active child contracts associated with each. What is the best measure in Power BI for this type of count?

Measure #2: Create an allocation using measure #1 as the denominator (i.e. total active contracts per pool). Since this will be a measure applied to the table with each contract as a row, I'd expect the numerator to be 1 (i.e. each contract).

With the proper formulas, I'd expect the following outcome for each measure:

Contract ID	Parent Contract	Status	Measure #1: Active Child in Parent Contract	Measure #2: Allocation of Parent Contract
123456	A12230	Active	3	33%
789101	A12230	Active	3	33%
827381	A12230	Active	3	33%
647325	A12230	Terminated	0	0%
123412	B16678	Active	1	100%
123433	-	Active	1	100%

Does that make more sense?

Anonymous · ‎04-26-2021

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 · ‎04-21-2021

@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 · ‎04-21-2021

@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

Anonymous · ‎04-21-2021

Thanks Denis-- good idea.

When I do that, the table spins and spins. So now I'm thinking I dont have the correct formula for the denominator.

When I originally ran the measure for Active Child Count, I performed this in a separate report page where I only listed the Parent contracts and performed a DISTINCTCOUNT of the child contracts for each of those-- effectively creating a view similar to a pivot table in excel.

Parent Contract	Active Child Count
A12230	2
B16678	1

When I pull that measure into the original table with all child contracts, it's not able to read a DISTINCTCOUNT for each contract (and if it did, I'd assume the answer would be 1 since it's just looking up to its data source column).

Is there a better measure to obtain the denominator of the allocation where the denominator should be the total count of child contracts within a given parent contract?