Forum Discussion
!Creating a measure referencing other measures
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
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?
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