DAX Calculation (Row by Row Division)

[esingh]

Hi 

I am new to Power BI and facing issues to create a dax calculation to achieve the following result:

Based on filter selection on ID,  headerString we need to compute the ratio. The ratio are to be calculated based on each institution name. (Refer the table below)

e.g For ABC Insitute we need to divide the response count of header string A for id 6 with Response count of header string B for id 6 .i.e 1401233/59 for ABC and similarly 1401000/54 for DEF etc. 

Thanks 

Ekam

id	categoryCode	optionString	headerString	responseString	responseCount	institution_name	institution_code
6	POST		A		1401233	ABC	10
6	POST		A		1401000	DEF	20
6	POST		A		0	GHI	30
6	POST		A		0	JKL	40
6	POST		B		59	ABC	10
6	POST		B		53	DEF	20
6	POST		B		0	GHI	30
6	POST		B		0	JKL	40

[esingh]

Thanks.. Just one more clarification.. we can even add [id] in the filter condition to get the values if ids are different.

[123abc · Accepted solution]

Create a DAX measure to calculate the ratio by dividing the responseCount of headerString A by headerString B for each institution.

Institution Ratio =
VAR responseA =
CALCULATE(
SUM('Table'[responseCount]),
'Table'[headerString] = "A"
)
VAR responseB =
CALCULATE(
SUM('Table'[responseCount]),
'Table'[headerString] = "B"
)
RETURN
IF(responseB = 0, BLANK(), responseA / responseB)

• The measure first calculates the sum of responseCount for headerString A and headerString B within the selected filter context (by institution_name).
• Then it divides the result for headerString A by the result for headerString B.
• It checks if the denominator (responseB) is zero to avoid division errors, returning BLANK() when there's no valid data.

You can place this measure in a table visualization with institution_name to see the ratios for each institution.