cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
elf_ranger91
Frequent Visitor

subtract row values (where values are measure) *circular dependency issue*

Hi Guys,

 

I have a table with 3 columns as shown below.  The column "sales gap (usd) column)" is a measure that has a relationship (that is to say, has a connection) with "remaining months to recognize" but the measure is not directly dependent on it.

power bi table.png

 

Now I want to find the difference between the row values in the "sale gap (usd)" column. I add a new column and write this expression: 

 

Diff. in Sales Gap =

             'New Deals Goal'[Sales Gap (USD) column] -

                     IF('New Deals Goal'[Index]=0; 'New Deals Goal'[Sales Gap (USD) column];

                                                     LOOKUPVALUE('New Deals Goal'[Sales Gap (USD) column];'New Deals Goal'[Index];'New                                                                                  Deals Goal'[Index]-1))

As seen below:

 

table2.png

 I have a circular dependency issue. I have read about it and I understand the problem, but how do I solve this?

 

Thank you. 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Baskar Resident Rockstar
Resident Rockstar

Re: subtract row values (where values are measure) *circular dependency issue*

Hi,

 

Try this one .

 

 

Column =
var A= CALCULATE( MAX('New Deals Goal'[Index]))-1
return
If('New Deals Goal'[Index]=0,
'New Deals Goal'[Sales Gap (USD) column],
'New Deals Goal'[Sales Gap (USD) column] - CALCULATE(SUM('New Deals Goal'[Sales Gap (USD) column]),FILTER('New Deals Goal','New Deals Goal'[Index]=A))
)

View solution in original post

1 REPLY 1
Baskar Resident Rockstar
Resident Rockstar

Re: subtract row values (where values are measure) *circular dependency issue*

Hi,

 

Try this one .

 

 

Column =
var A= CALCULATE( MAX('New Deals Goal'[Index]))-1
return
If('New Deals Goal'[Index]=0,
'New Deals Goal'[Sales Gap (USD) column],
'New Deals Goal'[Sales Gap (USD) column] - CALCULATE(SUM('New Deals Goal'[Sales Gap (USD) column]),FILTER('New Deals Goal','New Deals Goal'[Index]=A))
)

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors