DAX - Calculate scores using data from more than one column

[LRCSG]

Hello,

I have a situation where I have a table of results that is:

Survey ID	Restaurant 1	Restaurant 1 Service Satisfaction:	Restaurant 2	Restaurant 2 Service Satisfaction:	Restaurant 3	Restaurant 3 Service Satisfaction:
1000001	Dutch Coffee	10
1000002	Baba Chop	9	Dutch Coffee	7	Soo Bar	5
1000003	Dutch Coffee	8	Soo Bar	9
1000004	Soo Bar	9	Square Pizza	5
1000005	Square Pizza	5
1000006	Square Pizza	6			Dutch Coffee	10
1000007	Baba Chop	2	Dutch Coffee	10
1000008	Soo Bar	7	Baba Chop	10	Dutch Coffee	7
1000009	Baba Chop	8	Square Pizza	5
1000010
1000011	Soo Bar	10	Baba Chop	10
1000012	Square Pizza	9	Soo Bar	10
1000013	Dutch Coffee	8	Soo Bar	7
1000014
1000015	Soo Bar	9	Baba Chop	10	Square Pizza	3

and I am trying to get the following results from the survey responses in the table above.

• Overall service satisfaction is 48.2% 
• Dutch Coffee service satisfaction is 43%
• Baba Chop service satisfaction is 66.7%
• Soo Bar service satisfaction is 62.5%
• Square Pizza service satisfaction is 16.7%

Here's what I came up with and I can't get get a way to do this.

Here's one DAX I tried.

Restaurant Service Satisfaction =

DIVIDE(

     CALCULATE(

         COUNT('Table1'[Restaurant 1 Service Satisfaction:])

              && COUNT('Table1'[Restaurant 2 Service Satisfaction:])

              && COUNT('Table1'[Restaurant 3 Service Satisfaction:]),

FILTER(

     'Table1',

             'Table1'[Restaurant 1 Service Satisfaction:]*1>=9

                      && 'Table1'[Restaurant 2 Service Satisfaction:]*1>=9

                      && 'Table1'[Restaurant 3 Service Satisfaction:]*1>=9)),

     CALCULATE(

            COUNT('Table1'[Restaurant 1 Service Satisfaction:])

                     && COUNT('Table1'[Restaurant 2 Service Satisfaction:])

                     && COUNT('Table1'[Restaurant 3 Service Satisfaction:]),

FILTER(

     'Table1',

           'Table1'[Restaurant 1 Service Satisfaction:]*1>=1

                     && 'Table1'[Restaurant 2 Service Satisfaction:]*1>=1

                     && 'Table1'[restaurant 3 Service Satisfaction:]*1>=1)))

[v-xiaotang]

Hi @LRCSG 

Thanks for reaching out to us.

>>

• Overall service satisfaction is 48.2% 
• Dutch Coffee service satisfaction is 43%
• Baba Chop service satisfaction is 66.7%
• Soo Bar service satisfaction is 62.5%
• Square Pizza service satisfaction is 16.7%

Not sure how you calculated the expected results above, from the description of the post it doesn't seem to mention the calculation formula. Could you share it? thanks.

>>How can I attach a PBI file here?

You can refer to my red text at the bottom of this post, or refer to this link I just answered

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Total-sales-Relative-Date-Show-sales-for-all-...

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[LRCSG]

Hello @v-xiaotang and thank you for offering assistance.

I obtained the following results by using this manual calculation for each of the restaurants:

Example:

Dutch Coffee Service Satisfaction =

Count all 9 & 10 scores given to dutch coffee from the columns

[Restaurant 1 Service Satisfaction:]

[Restaurant 2 Service Satisfaction:]

[Restaurant 3 Service Satisfaction:]

Divide

Count all responses given to dutch coffee from the columns

[Restaurant 1 Service Satisfaction:]

[Restaurant 2 Service Satisfaction:]

[Restaurant 3 Service Satisfaction:]

Thus

Dutch Coffee Service Satisfaction = 3 divide 7 = 43%

[LRCSG]

Hello @JihwanKim, if you have time to take a look at this, I'll greatly appreciate any help I can get.

[Anonymous]

Hi

Try this. I had created a table (Postes) with all keyword in Restaurant 1 to solve this.

test =

var target=SELECTEDVALUE(postes[Restaurant 1])

var ttlrest1=calculate(COUNTROWS(FILTER(Feuil3,Feuil3[Restaurant 1]=target)))

var ttlrest2=calculate(COUNTROWS(FILTER(Feuil3,Feuil3[Restaurant 2]=target)))

var ttlrest3=calculate(COUNTROWS(FILTER(Feuil3,Feuil3[Restaurant 3]=target)))

var totalTarget = ttlrest1+ttlrest2+ttlrest3

var ttlrest1sup9c=calculate(COUNTROWS(FILTER(Feuil3,Feuil3[Restaurant 1]=target)),Feuil3[Restaurant 1 Service Satisfaction:]>=9)

var ttlrest2sup9=calculate(COUNTROWS(FILTER(Feuil3,Feuil3[Restaurant 2]=target)),Feuil3[Restaurant 2 Service Satisfaction:]>=9)

var ttlrest3sup9=calculate(COUNTROWS(FILTER(Feuil3,Feuil3[Restaurant 3]=target)),Feuil3[Restaurant 3 Service Satisfaction:]>=9)

var totalTargetSup9 = ttlrest1sup9c+ttlrest2sup9+ttlrest3sup9

return

divide(totalTargetSup9,totalTarget)

[LRCSG]

Hello @Anonymous ,

Thank you for trying to help.

I tried your method but I can't figure out why it does not give me the same outcome.

Would you mind pointing me in the right direction? How can I attach a PBI file here?