Weighted Average Not Summarizing Properly

[DQuigg]

I have a table that computes a weighted average of jobs times abilities. It basically computes the average of ability for an individual job. Then it computes an average of jobs by occupation. Finally it marries the two into a weighted average.

Everything works great except the ability is not subtotaling properly. If you add up the individual weighted average percentages, they do not total to the subtotal in the ability line. I don't know enough about SUMX to try to make it iterate to the proper number. In addition, this is a massive table and I would be concerned with performance. The individual DAX functions for each column follow. I have to believe it is a simple solution, but I have spent hours trying to find it.

Best to think of the first three columns as a template.

Individual Occupation Individual Ability = CALCULATE(SUM('Abilities Detail'[Data Value]),'Abilities Detail'[Scale ID]="LV").

This is a straight table calculation.

Individual Occupations All Abilities = CALCULATE(SUM('Abilities Detail'[Data Value]),'Abilities Detail'[Scale ID]="LV",ALLSELECTED(Abilities))

This computes the denominator for the weighted average ability by job.

Individual Occupation Relevant Ability Score = DIVIDE([Individual Occupation Individual Ability],[Individual Occupations All Abilities],BLANK())

Divides the first two columns

Job Postings All Unique = CALCULATE(DISTINCTCOUNT('_Job Postings'[Indeed Posting ID]))

Straight table calculation that computes distinct jobs by occupation when applied to the occupation context.

Job Postings All Unique Selected Occupations = CALCULATE(DISTINCTCOUNT('_Job Postings'[Indeed Posting ID]),ALLSELECTED('Occupations (SOC Structure)'))

Computes denominator for all jobs

Job Postings All Unique % All Selected = DIVIDE([Job Postings All Unique],[Job Postings All Unique Selected Occupations],BLANK())

Computes the % of jobs for each occupation

Relevant Ability Score = 'Job Market Metrics'[Individual Occupation Relevant Ability Score]*[Job Postings All Unique % All Selected]

Computes the weighted average of the abilities times the weighted average of the jobs. 

At the job and ability level, it computes correctly. However, when it rolls up to the ability level, it does not summarize it. I am not sure if this is a problem with iterating or I need to put something different in my filters.

Any help is greatly appreciated. 

[DQuigg · Accepted solution]

Thank you both very much for pointing me at least in the right direction. Through trial and error and using the quick calculation option in Power BI for weighted average, the following DAX formula worked.

Weighted Relevant Ability Score =

VAR __CATEGORY_VALUES = VALUES('Occupations (SOC Structure)'[ONET Occupation])

RETURN

            SUMX(

            KEEPFILTERS(__CATEGORY_VALUES),

            CALCULATE(

                [Individual Occupation Relevant Ability Score]

                    * [Job Postings All Unique % All Selected]

            ))

[amitchandak]

@DQuigg , I am not sure on the second column in row by option. But you have to try like

AverageX(summarize(Table,table[Ability],Table[View 2],"_1",[Individual Occupation Relevant Ability Score]
,"_2",[[Job Postings All Unique % All Selected]]),[_1]*[_2])

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also this:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...