Reusing the same code for different columns and take average

Anonymous — Tue, 28 Mar 2023 13:25:43 GMT

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),

LSAT_Score

)

Re: Reusing the same code for different columns and take average

tamerj1 — Tue, 28 Mar 2023 18:03:22 GMT

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 )

topic Reusing the same code for different columns and take average in DAX Commands and Tips

Reusing the same code for different columns and take average

Re: Reusing the same code for different columns and take average