locked
Calculate(SUM()) and Circular Dependency issues RRS feed

  • Question

  • I need some help to understand how to use calculate with two different tables.

    I have a table (YTD) with information of invoices and their amounts, over 500k lines on it. These are from different countries so and I need their gross to be converted to USD. To identify the country which they belong, they are related to a second table, which names the customer name and the country (CustomerData). Finally, the rates for each country are linked to the CustomerData through the Country name, so the relations are as below:

     

    YTD(CustomerCode)>CustomerData(CustomerCode)

    CustomerData(CountryName)>Currency(Country).

    Now, to convert the gross total from YTD to USD I used the below formula:

    =IFERROR(CALCULATE(SUM('ytd'[Gross Value])/SUM(Currency[Average])),0)

    The iferror is only there because some invoices do have the gross information, so it avoids the divided by 0 issue.

    Now, if I try to create another calculate column, it will popup an error with Circular Dependency. Researching I got to understand that it happens because how the engine works, but I could not understand the logic of it, mostly important, I was unable to figure out a solution so I can create two or more Calculated Columns. Basically there are other column on YTD table that I need to convert to USD, but I cannot because of the error.

    What am I missing here? What I was supposed to be doing, I tried using =sum(columname)/sum(columname) but it does not work, when using different tables, the result is just wrong, and calculate seems to require the type of operation (sum, average, etc). Looking over the internet I did not found any information on how to make that for different tables, only for the same table.

    Could someone help me out understanding what are the requirements here?

    Thank you very much.

    Monday, September 29, 2014 8:03 PM

Answers

  • Reading your description it seems you don't have a clear picture about why and where using CALCULATE. If you have a specific and detailed question, the forum is the right place, but I think it's not productive trying to learn by getting corrections in a forum.

    If you are using CALCULATE in a calculated column, you should be aware of the context transition happening (row context transformed into a filter context) and you should have a good reason to do that. If this does not seems familiar to you, I suggest you reading many articles, books, and threads in this forum about this topic! :)


    Marco Russo (Blog, Twitter, LinkedIn) - sqlbi.com: Articles, Videos, Tools, Consultancy, Training
    Format with DAX Formatter and design with DAX Patterns. Learn Power Pivot and SSAS Tabular.

    Tuesday, September 30, 2014 10:04 PM

All replies

  • First, DAX has a DIVIDE() function which should perform better than an IFERROR() wrapper, which handles divide by 0 errors gracefully (third argument defines alternate return value). This also prevents other potentially useful errors from being suppressed in the formula.

    Second, the solution to your problem will be to use the following format for your calculated columns:

    CALCULATE( 
        , <expression>
        , <your necessary filter arguments>
        , ALL( <calculated column 1>
            , <calculated column 2>
            , ...
            , <calculated column n>
        )
    )

    There's a good discussion on some of the intricacies of what is going on here in this thread.

    Monday, September 29, 2014 8:33 PM
  • Thanks for pointing the DIVIDE, the IFERROR is heritage of Excel as a good workaround for the DIV/0 issue, and thanks for taking time to go through my question!

    I could not make it work, I read the link you provided and to tell the truth it made me even more confuse, I am not very good with SQL yet, just the basics and maybe this is the problem here.

    I tried to build the formula with you example.

    =CALCULATE(DIVIDE('2014'[Gross Value],Currency[Average],0),ALL('2014'[Gross Value]),ALL(Currency[Average]))

    I get an error. It says: "It is not possible to determine the value of column 'Gross Value' in your table '2014' in the current context..."

    The same error happens if I only specify only one table/column inside the ALL. I do not have any filtering that I would like to do at the moment, but I feel like that this is the part I am missing here.

    From this, could you figure out where my logic is not working? I am feeling quite clueless right now.

    -----

    Extra bit of information, the error is the same even if I remove the ALL part of the syntax, so it makes me wonder that I am using DIVIDE wrong... but I really cannot see how, then I got the relationship.

    Would the problem lies because there is no direct relation between '2014' and 'Currency'? These tables are connect through 'CustomerData'. I cannot connect them directly because there is no field to match between them, it would be necessary to be added manually and directly on the table, which will be quite painful even with the use of formula because of the number of lines. But I am also not convinced this is the problem, because that kind of connection/Filter would work on MySQL, and it actually works when using =CALCULATE(SUM()), so... I don't know.


    • Edited by Estevao Santiago Tuesday, September 30, 2014 2:21 PM more information
    Tuesday, September 30, 2014 2:09 PM
  • The reason for the circular dependency is that you have two calculated columns using CALCULATE in a table without a key - see this article: http://www.sqlbi.com/articles/understanding-circular-dependencies/


    Marco Russo (Blog, Twitter, LinkedIn) - sqlbi.com: Articles, Videos, Tools, Consultancy, Training
    Format with DAX Formatter and design with DAX Patterns. Learn Power Pivot and SSAS Tabular.

    Tuesday, September 30, 2014 3:03 PM
  • Hi Marco, I have loaded the 2014 table through PowerQuery, the data for this table is actually 8 spreadsheets appended in one query. On PowerQuery I have set a Index Column but the problem persists. I tried using the Index column created on the 2014 table on the ALL argument, but it does not work either, it says "the Function ALL was expecting a table reference expression for argument '2', but it was used a chain expression of characters"... No idea what that means, I experimented different ways of building the formula with ALL, but the error is the same, so I assume the argument '2' must be something related to my CALCULATE(DIVIDE()). But once again I cannot figure what I am doing wrong.

    On a side note, now when I use the CALCULATE(SUM()) it no longer calculates correctly the Gross of each line, the product of all Lines is the same value, so I think using PowerQuery to load the 2014 data was not a good idea as at least the calc was working when I only used PowerPivot.

    I found some info on compatibility issues between PowerPivot for Excel 2010 and PowerQuery, because the database engine versions are different, but the problems described does not quite fit the ones I am facing so I guess I can just ignore them trying to find my solution.

    Still trying here, but please feel free to help, I would appreciate a lot any tips :)

    Tuesday, September 30, 2014 6:45 PM
  • If you could provide some sample data and an example of your expected result we could probably more readily help.
    Tuesday, September 30, 2014 9:05 PM
  • Reading your description it seems you don't have a clear picture about why and where using CALCULATE. If you have a specific and detailed question, the forum is the right place, but I think it's not productive trying to learn by getting corrections in a forum.

    If you are using CALCULATE in a calculated column, you should be aware of the context transition happening (row context transformed into a filter context) and you should have a good reason to do that. If this does not seems familiar to you, I suggest you reading many articles, books, and threads in this forum about this topic! :)


    Marco Russo (Blog, Twitter, LinkedIn) - sqlbi.com: Articles, Videos, Tools, Consultancy, Training
    Format with DAX Formatter and design with DAX Patterns. Learn Power Pivot and SSAS Tabular.

    Tuesday, September 30, 2014 10:04 PM