Circular Dependency when using variable in measures

[analyticspbi87]

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.

[Anonymous]

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.

[lbendlin · Accepted solution]

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 )

[analyticspbi87]

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 

https://www.dropbox.com/scl/fo/fztltp5tdoipz3lugw5gk/ANKot9kvrP5XcN4SwFpb91s?rlkey=svodmouimp1lrlaxq...