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