• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Pivot - creating a new Calculated column

E

Excel_student1256

New Member
New to Power Pivot and still learning its capabilities:
In Excel, I created about 8 columns where each column uses the previous column in its formula, and I'm trying to replicate this in power pivot.

I hit a snag when I created this "sumif" calculated column ('CC1') in power pivot ~ =CALCULATE(SUM([BM]),Sheet1[BM Month]="Base Merit Month" ~. When trying to use 'CC1' in the formula for the next calculated column, for example, =[CC1] * 1.32 , it gives me a circular dependency error:

1735340310298.png

Just using excel logic, I'm not seeing the source of the dependency...but maybe excel can more easily manipulate columns than power pivot, unless there's just some logic I'm missing.

Can 'CC1' be used in another calculated column without this circular dependency? Are there any substitute formulas or workarounds I could apply?

Can provide additional detail.
Thanks.
 
A picture is worth a thousand words, but an excel file tops them both. Attach a sample file with the issue and a mock up of what your expected results should look like.
 
AlanSidman said:
A picture is worth a thousand words, but an excel file tops them both. Attach a sample file with the issue and a mock up of what your expected results should look like.
Click to expand...

Certainly, attached is the sample file with column i being the result column, and to the right are screenshots attempting to replicate this table in Power Pivot but getting the circular dependency error.
 

Attachments

  • Example Excel.xlsx
    98.2 KB · Views: 0
If you use CALCULATE in a calculated column, the entire row context is used, which includes every column in the table. If you do that more than once, then you create a circular dependency. You would need to remove the relevant filters from columns to avoid that, but I can't see any reason at all to create that second column, rather than using a measure. I'm not convinced the first one makes a lot of sense either. The data model does not work the same way as a table in Excel and you try and literally replicate one in the other.

For more detail see here: https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/
 
Last edited:
You must log in or register to reply here.
Back
Top