Question

How to calculate the sum of a ratio where each row’s numerator and denominator depend on a third column?

In Power BI, I need to calculate a total by summing up row-level ratios. However, both the numerator and denominator for each ratio depend on a value from a third column. I need help designing a DAX measure or calculated column that correctly computes and aggregates these ratios.

Answer 1

Assume:

• Numerator is used when Type = "A"

• Denominator is used when Type = "B"

Create two helper columns first (optional but clearer):

NumeratorValue = IF(YourTable[Type] = "A", YourTable[Value], BLANK())
DenominatorValue = IF(YourTable[Type] = "B", YourTable[Value], BLANK())

Now create the Ratio Column per row:

RowRatio =
VAR Num = CALCULATE(SUM(YourTable[Value]), FILTER(YourTable, YourTable[ID] = EARLIER(YourTable[ID]) && YourTable[Type] = "A"))
VAR Den = CALCULATE(SUM(YourTable[Value]), FILTER(YourTable, YourTable[ID] = EARLIER(YourTable[ID]) && YourTable[Type] = "B"))
RETURN
DIVIDE(Num, Den)

Then create a measure to sum all valid row-level ratios:

TotalRatioSum = SUMX(YourTable, YourTable[RowRatio])