Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Reusing the same code for different columns and take average

I am trying to create a calculation, that takes in a column and outputs a value that I am going to later use to average all the values from all those columns. Issue is I have 9 columns and it seems like a not-so-optimized solution to rewrite the code again and again. Is there any way I can pass value in the function and based on those values returned from that function, create average score. 

 

This is my code for one column, lets say we have 9 more columns like this e.g pace, moderation etc 

 

LSAT_Confidence Score demo =
 
//be sure you have a question in your survey asking "How likely would you be to recommend us?" with a scale of 0-10
VAR _tbl= FILTER('nps',NOT(ISBLANK('nps'[Confidence boosting])))
VAR count_1 = COUNTROWS(FILTER(_tbl,'nps'[Confidence boosting]==1))
VAR count_2 = COUNTROWS(FILTER(_tbl,'nps'[Confidence boosting]==2))
VAR count_3 = COUNTROWS(FILTER(_tbl,'nps'[Confidence boosting]==3))
VAR count_4 = COUNTROWS(FILTER(_tbl,'nps'[Confidence boosting]==4))
VAR count_5 = COUNTROWS(FILTER(_tbl,'nps'[Confidence boosting]==5))
VAR count_6 = COUNTROWS(FILTER(_tbl,'nps'[Confidence boosting]==6))
VAR count_7 = COUNTROWS(FILTER(_tbl,'nps'[Confidence boosting]==7))
VAR count_8 = COUNTROWS(FILTER(_tbl,'nps'[Confidence boosting]==8))
VAR count_9 = COUNTROWS(FILTER(_tbl,'nps'[Confidence boosting]==9))
VAR count_10 = COUNTROWS(FILTER(_tbl,'nps'[Confidence boosting]==10))
 
VAR sum_of_counts =  count_7 + count_8 + count_9 + count_10
VAR sum_of_all_counts =  count_1 + count_2 + count_3 + count_4 +count_5 +count_6 +count_7 + count_8 + count_9 + count_10
 
VAR LSAT_Score = (sum_of_counts/sum_of_all_counts)*100

 
RETURN If(
                ISBLANK(LSAT_Score),
                0,
                LSAT_Score
           )

1 Reply

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Anonymous 

    please try

    LSAT_Confidence Score demo =
    VAR sum_of_counts =
    COUNTROWS ( FILTER ( 'nps', 'nps'[Confidence boosting] IN { 7, 8, 9, 10 } ) )
    VAR sum_of_all_counts =
    COUNTROWS (
    FILTER (
    'nps',
    'nps'[Confidence boosting] IN { 0, 1, 2, 3, 4, 5, 7, 8, 9, 10 }
    )
    ) -- if there are no numbers other than 0 - 10 then it can be 'nps'[Confidence boosting] <> blank ()
    VAR LSAT_Score = ( sum_of_counts / sum_of_all_counts ) * 100
    RETURN
    IF ( ISBLANK ( LSAT_Score ), 0, LSAT_Score )