Forum Discussion

Dog's avatar
Dog
Responsive Resident
9 years ago

Calculate function within calculated columns.

Hi All,    not sure if anyone has come across this before but I'm slowly questioning my sanity over the following.    I have recently completed work for a client which implements dynamic ranking ...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

Tough to say without the data, rounding?

  • Dog's avatar
    Dog
    Responsive Resident
    9 years ago

    Hi, 

     

    not rounding nope just whacky figures like 1.09993353544e-10

    which display as zero. 

     

    Ive moved on slightly since then and it looks like it may have been an issues with the number of records. 

    I've replaced my original calculated table by summarizing at a higher level and thus reducing hte number of records. 

    not ideal as I've had to implement several more layers of filters but it's certaintlt getting closer. 

     

    have you ever heard of using calculate in a calculated column on a table with over 1 mil records causing a rather large zero value with many decimals. 

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      9 years ago

      Nope, that's a new one. Can you at least post your CALCULATE formula? If I can dig up a table with a million rows or generate one I'd be interested to test it out.

      • Dog's avatar
        Dog
        Responsive Resident
        9 years ago

        Fixed Assets CY", CALCULATE ([SumOfBalance(CY)],Nominals[BSSummary] = "Fixed Assets", Nominals[DepreciationYN] = FALSE()),
        "Fixed Assets PY", CALCULATE ([SumOfBalance(PY)], Nominals[BSSummary] = "Fixed Assets", Nominals[DepreciationYN] = FALSE()),

         

        the above two are taken from the summarize table statement. 

         

        the measures SumOfBalance(CY) and (PY) are basic SUM([Balance]) measures. 

        (CY) measure is summing the balance from the "Current Year" table of transactions. 

        (PY) measure is summing the balance from the "Previous Year" table of transactions. 

         

        (only split into two tables because nothing worked when querying the whole transaction details tables) 

         

        CY table consists of 600k records 

        PY 1,200k records. 

         

        CY returns valid results and correct ones. 

        PY does not. 

         

        the two tables are created like this 

        CALCULATETABLE('Transaction Details', Dates[Financial Year Name] = "Current Year", 'Transaction Details'[Transaction Type] = "Actual")

         

        CALCULATETABLE('Transaction Details', Dates[Financial Year Name] = "Previous Year", 'Transaction Details'[Transaction Type] = "Actual")