Help: Calculating response rate in same column

[J_Mug]

Hi,

I have a matrix with my NPS and Number of responses by source, I wanted to add the response rate to that matrix. The problem is that there are 3 response rate measures in my model (Because I have 3 denominators for Response rate) so when I include them there is not a single response rate column/row but 2. 

I need to have the response rate from different sources under the same row in this visual. This response rate is calculated in the measure level as I have my NPS data with my FeedbackSource data as column in one table and then I my denominators for the rate (visits to the web, transactions, surveys sent, etc) all by date. The problem here is that the divide is done by two different denominators, one the web visits and the other the Transactions for the QR.

As of now I calculate the response rate with the following formula:

1. DIVIDE(CALCULATE(COUNT('Table'[FeedbackSource]);FILTER('Table';'Table'[FeedbackSource]="Web"));SUM('Table2'[CountWebVisits]);0)
2. DIVIDE(CALCULATE(COUNT('Table'[FeedbackSource]);FILTER('Table';'Table[FeedbackSource]="QR"));SUM('Table2'[CountQR]);0)

I cannot divide by the same thing and thats why I have to add different measures. Is there any way I could achieve this? The main objective is for the matrix to display the measures as one, although there maybe more than 2. To do this I'd either have to "cheat" (like if naming them the same would make them appear in the same row) or calculate the two measures as one (but again I am dividing the feedback sources by their respective metric so its a different Denominator.

I have tried to use a VAR and Return structure. The problem is that the if doesnt work in the column level, the idea was to do something like this:

Var Web = DIVIDE(CALCULATE(COUNT('Table'[FeedbackSource]);FILTER('Table';'Table'[FeedbackSource]="Web"));SUM('Table2'[Count]);0)

Var QR = DIVIDE(CALCULATE(COUNT('Table'[FeedbackSource]);FILTER('Table';'Table[FeedbackSource]="QR"));SUM('Table2'[Count]);0)

Return

If('Table'[FeedbackSource]="Web";Web;IF('Table'[FeedbackSource]="QR;"QR").

But it wont work because the IF doesnt work with something in the context of a column.

Kind regards,

[v-lili6-msft · Accepted solution]

hi @J_Mug 

You may need to use SELECTEDVALUE or MAX in the formula as below:

Var Web = DIVIDE(CALCULATE(COUNT('Table'[FeedbackSource]);FILTER('Table';'Table'[FeedbackSource]="Web"));SUM('Table2'[Count]);0)

Var QR = DIVIDE(CALCULATE(COUNT('Table'[FeedbackSource]);FILTER('Table';'Table[FeedbackSource]="QR"));SUM('Table2'[Count]);0)

Return

If(SELECTEDVALUE('Table'[FeedbackSource])="Web";Web;IF('Table'[FeedbackSource]="QR;"QR").

or

Var Web = DIVIDE(CALCULATE(COUNT('Table'[FeedbackSource]);FILTER('Table';'Table'[FeedbackSource]="Web"));SUM('Table2'[Count]);0)

Var QR = DIVIDE(CALCULATE(COUNT('Table'[FeedbackSource]);FILTER('Table';'Table[FeedbackSource]="QR"));SUM('Table2'[Count]);0)

Return

If(MAX('Table'[FeedbackSource])="Web";Web;IF('Table'[FeedbackSource]="QR;"QR").

If you still have the problem, please share your sample pbix file and your expected output.

Regards,

Lin