Forum Discussion
calculating multiple values within 2 columns
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
Hi Anonymous ，
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,
KellyDid I answer your question? Mark my post as a solution!
6 Replies
Hi Anonymous ，
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,
KellyDid I answer your question? Mark my post as a solution!v-kelly-msft: this works super!!! thanks a lot!!
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!!
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
Try like
GT = calculate(sum(table[NR],all(Table)) Promoter = calculate(sum(table[NR],NPS >=9) Detector = calculate(sum(table[NR],NPS <=6)
