Forum Discussion
Circular Dependency when using variable in measures
Hi there,
Currently, I'm trying to get two different measures to show as follows and am getting an error on the second variable.
| Building | Year | Value | Type Cost | Type | Value Thousands | Group | TOTAL A | TOTAL B | Multiplier |
| A | 2021 | 123 | Amort Year | Recoverable | 500 | B | 5,000.00 | 6,000.00 | 1.5 |
| A | 2021 | 123 | Add Year | Recoverable | 100 | A | 5,000.00 | 6,000.00 | 1.3 |
| A | 2022 | 124 | Add Year | Recoverable | 30 | B | 5,000.00 | 6,000.00 | 1.69 |
| A | 2023 | 125 | Add Year | Non-Recoverable | 200 | B | 5,000.00 | 6,000.00 | 1.4 |
| A | 2024 | 126 | Add Year | Non-Recoverable | 0 | B | 5,000.00 | 6,000.00 | 1.5 |
| A | 2025 | 127 | Add Year | Non-Recoverable | 0 | B | 5,000.00 | 6,000.00 | 1.8 |
| B | 2021 | 123 | Add Year | Recoverable | 500 | A | 3,000.00 | 4,000.00 | 1.3 |
| B | 2022 | 124 | Add Year | Recoverable | 100 | B | 3,000.00 | 4,000.00 | 1.69 |
| B | 2023 | 124 | Add Year | Non-Recoverable | 30 | B | 3,000.00 | 4,000.00 | 1.4 |
| B | 2024 | 124 | Add Year | Non-Recoverable | 200 | B | 3,000.00 | 4,000.00 | 1.5 |
| B | 2025 | 125 | Add Year | Non-Recoverable | 0 | B | 3,000.00 | 4,000.00 | 1.8 |
| B | 2021 | 125 | Amort Year | Non-Recoverable | 0 | B | 3,000.00 | 4,000.00 | 1 |
The calculation I'm using is as follows. The following returns a 0
% of TOTAL A
VAR ANUMERATOR =
DIVIDE(
CALCULATE(
SUM('Data'[Value]),
'Data'[Group]<>"A" &&
'Data'[Type Cost]="Add Year"),
1000)
VAR ADENOMINATOR =
DIVIDE(
(SUM(Data'[Multiplier])*SUM('Data'[TOTAL A])),
1000)
RETURN ANUMERATOR/ADENOMINATOR
% of TOTAL B
VAR BNUMERATOR =
DIVIDE(
CALCULATE(
SUM('Data'[Value]),
'Data'[Group]<>"A" &&
'Data'[Type Cost]="Add Year"&&
'Data'[Type]="Non-Recoverable"),
1000)
VAR BDENOMINATOR =
DIVIDE(
(SUM(Data'[Multiplier])*SUM('Data'[TOTAL B])),
1000)
RETURN BNUMERATOR/BDENOMINATOR
Just wondering if anyone has any better formulas that can be used? When I split out the denominators and numerators, I kept getting errors.
You have a couple of typos in your formulas, but there is no circular dependency anywhere.
% of TOTAL A = VAR ANUMERATOR = DIVIDE ( CALCULATE ( SUM ( 'Data'[Value] ), 'Data'[Group] <> "A", 'Data'[Type Cost] = "Add Year" ), 1000 ) VAR ADENOMINATOR = DIVIDE ( SUM ( 'Data'[Multiplier] ) * SUM ( Data[ TOTAL A ] ), 1000 ) RETURN DIVIDE ( ANUMERATOR, ADENOMINATOR ) % of TOTAL B = VAR BNUMERATOR = DIVIDE ( CALCULATE ( SUM ( 'Data'[Value] ), 'Data'[Group] <> "A", 'Data'[Type Cost] = "Add Year", 'Data'[Type] = "Non-Recoverable" ), 1000 ) VAR BDENOMINATOR = DIVIDE ( ( SUM ( 'Data'[Multiplier] ) * SUM ( 'Data'[ TOTAL B ] ) ), 1000 ) RETURN DIVIDE ( BNUMERATOR, BDENOMINATOR )
3 Replies
You have a couple of typos in your formulas, but there is no circular dependency anywhere.
% of TOTAL A = VAR ANUMERATOR = DIVIDE ( CALCULATE ( SUM ( 'Data'[Value] ), 'Data'[Group] <> "A", 'Data'[Type Cost] = "Add Year" ), 1000 ) VAR ADENOMINATOR = DIVIDE ( SUM ( 'Data'[Multiplier] ) * SUM ( Data[ TOTAL A ] ), 1000 ) RETURN DIVIDE ( ANUMERATOR, ADENOMINATOR ) % of TOTAL B = VAR BNUMERATOR = DIVIDE ( CALCULATE ( SUM ( 'Data'[Value] ), 'Data'[Group] <> "A", 'Data'[Type Cost] = "Add Year", 'Data'[Type] = "Non-Recoverable" ), 1000 ) VAR BDENOMINATOR = DIVIDE ( ( SUM ( 'Data'[Multiplier] ) * SUM ( 'Data'[ TOTAL B ] ) ), 1000 ) RETURN DIVIDE ( BNUMERATOR, BDENOMINATOR )I have revised the dataset to a more similar structure and tried to make the formula in a new PBIX file and still getting circular reference errors.
I have attached them in the following link
Hi, analyticspbi87
Do you have to use calculated columns, please? Can you use Measure?
Can you share the results you expect to get and the logic behind the calculation?We will do our best to help you solve the problem you are having.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
