Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Create an Average calculation grouping by two different fields

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. 🙂
average
DAX
dynam
grouping
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ï¼Œ

     

    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.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ï¼Œ

     

    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.