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!
amitchandak's avatar
amitchandak
Super User
6 years ago

Try like

GT = calculate(sum(table[NR],all(Table))
Promoter  = calculate(sum(table[NR],NPS >=9)	
Detector  = calculate(sum(table[NR],NPS <=6)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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?