Forum Discussion

DQuigg's avatar
DQuigg
Advocate III
6 years ago
Solved

Weighted Average Not Summarizing Properly

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. 
Need Help
  • DQuigg's avatar
    DQuigg
    6 years ago

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

3 Replies