DAX Measure - SUM of a measure

[BTLYBSCT]

Hi Guys,

I have created a measure to give me a score based off an agents KPI which looks up a table I have created.

CC_KPI_SCORE = LOOKUPVALUE('CCO CC_KPI_SCORES_VIEW'[Score],'CCO CC_KPI_SCORES_VIEW'[KPI],AllDurationToSecMeasures[CC_KPI])

Now I am trying to create a measure to SUM the total of the score then divid it by rows with only values to get a overall score however the intellisense won't show the column which I assume is due to it being a measure.

I have though of different logics but nothing seems to be working and I am offically stuck.

I feel like I need to create some variables but I can't even calculate sum the column.

Any assistance would be much appreciated.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Zubair_Muhammad]

@BTLYBSCT

To aggregate a MEASURE,,,...you will have to use an iterator like SUMX

Try this pattern

Sum of MEASURE = SUMX(VALUES(Table1[KPI]),[Measure])

[BTLYBSCT]

Thanks Zubair, unfortunately that doesn't work, might have to go deeper using crossjoin to my lookup table and sum the value from there, I'll let you know how I go.

[v-jiascu-msft]

Hi @BTLYBSCT,

Seems you have KPI and score in a table, why did you still create a measure CC_KPI_SCORE? Can you share a sample of the original data and the expected result?

Best Regards,
Dale

[BTLYBSCT]

Hi @v-jiascu-msft,

Thanks for your response, still struggling to find the correct logic for this.

So for further context I have a measure to get the KPI (CC_KPI) which is based off an agents OutcomeDescription and how long it took to complete that call. It then looks at a table and spits out the KPI which ranges from Unsatisfactory to Outstanding.

InteractionDetails[Outcome Description]

[AVERAGE_HANDLE_TIME_ALL_IN_SEC]

CC_KPI = 
VAR AHT = [AVERAGE_HANDLE_TIME_ALL_IN_SEC]
VAR OD =  MAX(InteractionDetails[Outcome Description])
VAR KPI_VALUE = CALCULATE(max(Outcome_Description_KPI[Rating]),filter(Outcome_Description_KPI,Outcome_Description_KPI[Wrap Code] =OD && AHT >= Outcome_Description_KPI[Start] && AHT <= Outcome_Description_KPI[End]))
RETURN KPI_VALUE

Now based off that KPI I have created a measure to provide a score which is CC_KPI_SCORE which gives a fixed score based off the KPI.

CC_KPI_SCORE = LOOKUPVALUE('CCO CC_KPI_SCORES_VIEW'[Score],'CCO CC_KPI_SCORES_VIEW'[KPI],AllDurationToSecMeasures[CC_KPI])

My ultimate aim is to sum the score of an agent when people filter it and divide it against rows that have values and the result of that will look up another table to provide the overall KPI rating but I can't get the SUM part to work.

I have spent some time on this and taking a short break hoping to find some insperation after I clear my mind.