Is it to better to create Calculated Column/Column in DB or a Measure in Power BI for 200M records?

[HassanAshas]

I have a table containing 200M+ Records.

There's one column that is "transaction_amount" and I need to show this value in a Tabular view on my Power BI Report. Additionally, the table visual needs to be sorted by this transaction_amount column (however, I need to sort by the abstract value of this transaction_amount).

Currently, transaction_amount contains both positive and negative values (and I need the negative values as well for other visuals. I can't just make all values positive in this column but for this particular use case, I need all values to be positive).

So I am wondering, what should I do?Should I create a Measure and add that as an additional column in my table visual (and sort by that measure) or should I create a Calculated Column (or a Column in the Data Warehouse) for the abstract value of transaction_amount.

I know the ideal way to do this would be to create the additional column, but that would definitely increase the size of my "data model" as it would add 200M+ values in my dataset. So, that's why, I am wondering whether a measure would be a better choice here because measures don't increase the size of the dataset.

A follow up question on the same thing, what if my column contains all string values. And now, I need to apply some transformation on that column and create an additional column for a speicifc visual and use that second column values in a specific visual. Would you rather create a Measure or a column in this case? (Asking this because creating a column in this case would significantly increase the size of model due to the values being strings and non-compressable)

[danextian]

Hi @HassanAshas ,

Does transaction_amount column refer to a column in a table or a column in a table visual? If it is the former, then you really need to two calc  column  - one with both positive and negative and one with the abstract (absolute?) value. If it is the latter, you will still need to create two measures and put both in your visual - you cannot sort a measure by another measure unlike with columns. Bottomline: the approach depends on your requirements.

As for your other question, if the transformation doesn't involve scanning a table (think about ALLEXCEPT) it is better to create this in M for an ideal compression (read this: https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/) Note: strings are compressable. If you are too concerned about the model size, there are multiple ways to reduce it such as splitting datetime  columns into date and time columns, removing the seconds fromt timestamps, reducing decimal numbers, loading just the  rows and columns needed not because you think they're needed, etc.

[swikritee_p]

@HassanAshas , To calculate the A/B ratio accurately, you should use the divide measure instead of simply averaging the results of dividing A by B. The divide measure correctly calculates the sum of A divided by the sum of B, which is the true A/B ratio. Using the Avg measure, on the other hand, would result in an inaccurate representation of the A/B ratio.

divide(Sum(Table[Value1]), sum(Table[Value2]))

[HassanAshas]

@swikritee_p  I'm not sure if you understood my question clearly. 

The question is related to Calculated Column vs Measure, and not exactly A/B Ratio. 

[swikritee_p]

@HassanAshas , In general, measures are the go-to tool for calculations in Power BI. However, when dealing with large datasets and row-level calculations like A*B, measures can slow down the performance significantly. To address this issue, we can divide the calculation into two parts: creating a calculated column for the initial step and then using a measure for the final aggregation. This approach breaks down the complex calculation into smaller, more manageable steps, improving the overall performance.