I am working with a financial dataset where I need to calculate a specific ratio for each transaction before summing them up. The challenge is that the numerator and denominator change based on a 'Category' column. For example, if Category is 'A', I use Column X/Column Y, but if it's 'B', I need Column Z/Column Y. A simple division of totals won't work because I need the sum of the individual row-level ratios. How can I achieve this row-by-row logic in a measure without creating a calculated column that bloats my model?
3 answers
To solve this without adding a physical column, you must use an iterator function like SUMX. This function allows you to define an expression that evaluates for every single row of a table before summing the results. Your DAX would look something like this: Total Ratio = SUMX(YourTable, DIVIDE(SWITCH([Category], "A", [ColumnX], "B", [ColumnZ], 0), [ColumnY])). The SWITCH function inside the SUMX handles the conditional logic for the numerator perfectly. By using DIVIDE, you also ensure that any rows where the denominator is zero return a blank or a specified value instead of an error, which is a best practice for clean data science models.
This approach works well for small tables, but I’ve heard that iterators like SUMX can be quite slow on millions of rows. Is there a more performant way to handle this, perhaps by pre-calculating parts of the ratio in Power Query, or is the performance hit negligible for most standard business datasets
I prefer using SUMX because it keeps the model flexible. If the business logic for Category 'A' changes tomorrow, you only have to update one measure instead of re-running your entire data refresh.
Exactly, Amanda. I'd also add that you can use variables inside your SUMX to make it even more readable. For instance, define VAR Num = ... and VAR Den = ... then RETURN DIVIDE(Num, Den). This makes debugging much easier when you're looking at the results in a table visual, as you can temporarily return just the numerator variable to verify the SWITCH logic is firing correctly for every category.
That's a valid concern, James. For datasets under a few million rows, SUMX is generally very fast on the VertiPaq engine. However, if performance drags, you can move the conditional logic into Power Query during the ETL phase. By adding a "Custom Column" that performs the if...then logic and the division, you shift the computation from the report's "render time" to "refresh time." This makes the final DAX as simple as a SUM on that new column. If you are aiming for high-concurrency reports with many users, the Power Query route is often the more scalable SEO-friendly strategy.