Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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: ...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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,
    Kelly
     
    Did I answer your question? Mark my post as a solution!
v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

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,
Kelly
 
Did I answer your question? Mark my post as a solution!
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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!!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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