Calculate function within calculated columns.

[Dog]

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 across selected measures. 

this all works well but I've found the performance experience quite poor using measures alone

so, to get around this I have created a calculated table which pre calculates all of the totals for each of my locations, which I can then use to perform the ranking.

this all sounds straight forward so far. 

the issue i have is that some of the calculated columns are not calculating correctly, mainly those ones that are calculating from tables that have over 1,000,000 rows. 

any thoughts?

Dog

[Greg_Deckler]

Tough to say without the data, rounding?

[Dog]

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]

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]

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")