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 ...
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.

     

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