calculating multiple values within 2 columns

[emveha]

hi

in my report i have 2 columns, NPS and NR. The column NPS contains the value of the nrs 1-10 and the column NR contains a certain ID that is added as a count distinct value. It looks like this:

NPS   NR

0        123

1        22

10      1231

2        28

3        33

4        29

5        132

6        56

7        155

8        375

9        312

Tot=  2495

Now i want a percentage of the values of the NPS 9 and 10 ((1231+312)/2495)*100)- the percentage of the total of the NPR 0-6 ((122+22+28+33+29+132+56)/2495)*100. The end result of the calculation of the values of NPS9-10 = 62, the other calculation = 17, so 62-17=45 and that is what i want.

Any ideas how to do that?

thanks in advance

M

[v-kelly-msft · Accepted solution]

Hi @emveha ，

You need a measure as below:

Measure =
VAR a =
    CALCULATE (
        SUM ( 'Table'[NR] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[NPS  ] = 9 || 'Table'[NPS  ] = 10 )
    )
VAR b =
    CALCULATE ( SUM ( 'Table'[NR] ), ALL ( 'Table' ) )
VAR c =
    CALCULATE (
        SUM ( 'Table'[NR] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[NPS  ] <= 6 )
    )
RETURN
    FORMAT ( a / b - c / b, "percent" )

Here I checked the total number of NR is 2496,not 2495, so the result is 44.87%.

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[emveha]

@v-kelly-msft  the measure which you send me is not giving issues, but when i want to use this in my report i get this message:

Couldn't load the data for this visual
MdxScript(Model) (15, 43) Calculation error in measure 'chats_report'[%NPS]: DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.
Please try again later or contact support. If you contact support, please provide these details.
Activity ID: fda03d54-bdc5-4113-9bc5-e7a4531060e6
Request ID: a7107091-331a-2989-fa26-3ec1d725bfa9
Correlation ID: 2ce315ee-f840-8860-7714-31a7b3559121
Time: Mon Mar 02 2020 17:11:31 GMT+0100 (W. Europe Standard Time)
Service version: 13.0.12439.190
Client version: 2002.3.018
Cluster URI: https://wabi-us-north-central-redirect.analysis.windows.net/

the thing is that the data is not only presented as a number but also as text... So, for example you can have the values 0-10 but also YES and NO or YES1 and NO1.

Any idea how to solve that??

thanks again!!

[emveha]

@v-kelly-msft 

hi, am sorry to bother you again, but as explained your solution works but... see my remarks below.....

Could you please try to give a solution that will solve this issue as well!?

Appreciate it.

thanks in advance

[emveha]

@v-kelly-msft: this works super!!! thanks a lot!!

[amitchandak]

Try like

GT = calculate(sum(table[NR],all(Table))
Promoter  = calculate(sum(table[NR],NPS >=9)	
Detector  = calculate(sum(table[NR],NPS <=6) 

[emveha]

Hi Amitchandak, thanks for your swift response, just a remark the sum in the measure GT needs to be replaced with distinctcount but then i get an error message. Looking at your solution, i have to create 3 measures: GT, Promotor and Detector, correct?