Forum Discussion
DAX Calculation (Row by Row Division)
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 |
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.
2 Replies
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.
Thanks.. Just one more clarification.. we can even add [id] in the filter condition to get the values if ids are different.
