Create an Average calculation grouping by two different fields

[Nntsilvis]

I need to create the calculation for the avg score grouping by subtheme per each client(case ID).

I created a expected output based on some inputs values:

As you can see, first all i need to sum the score per subtheme, then divide each client score for the subtheme which its belongs. 

I have tried several formulas, such as:

Nothing works as expected: sometimes the values returned are the same as the score field for each client/caseid, or either it just calculate the sum of the score/count(total clients), what is not true, sice the nominator will variate depending of the score oh that or other specific client per suntheme each belongs.

 

If someone know how to solve it. i would appreciate a lot. 🙂

[Anonymous · Accepted solution]

Hi @Nntsilvis ，

Please use the following measures to meet your requirements:

SumPerSubtheme = CALCULATE(SUM('Table'[Score]),ALLEXCEPT('Table','Table'[Subtheme]))

AveragePerCaseIDBySum = DIVIDE(MAX('Table'[Score]),[SumPerSubtheme],0)

Result for your reference:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nntsilvis]

Follow the attampts i had tried:
Score Ratio1 = VAR Nominator = CALCULATE( SUM('fact_assessment'[Sub-theme score]), ALLEXCEPT(dim_client, dim_client[GCID]) ) VAR Denominator = CALCULATE( SUM('fact_assessment'[Sub-theme score]), ALLEXCEPT(dim_subtheme, dim_subtheme[Subtheme_id]) ) RETURN DIVIDE(Nominator, Denominator, 0)

Score Ratio2 = CALCULATE ( AVERAGE ( fact_assessment[Subtheme score] ), ALLEXCEPT ( fact_assessment, fact_assessment[Subtheme_id], fact_assessment[Case ID]) )

Score Ratio3 = AVERAGEX( SUMMARIZE( 'fact_assessment_scores', 'fact_assessment_scores'[Case ID], 'fact_assessment_scores'[Subtheme_id], "AvgScore", AVERAGEX( FILTER( 'fact_assessment_scores', NOT ISBLANK('fact_assessment'[Subtheme score]) ), 'fact_assessment_scores'[Subtheme score] ) ), [AvgScore] )