Calculate averages by company and aggregate these averages to get the total for all companies

[Anonymous]

Hi, I'm having trouble with a calculation.

My dataset is a monthly snapshot of policyholder counts by company and product. The policyholder counts are held at the company level (number of employees). I have data from April to September and there are multiple rows where there are multiple products for that month.

I need to calculate the policyholders per month for each company then average those monthly values across a date range to get the average policyholders across all months for each company. These averages then need to be summed in the report to give a total policyholders for all companies. The table on the left is the output i need, but with correct data. 

I'm using this calculation:

Total Policyholders = sumx(VALUES(Sheet1[Company Number]),

CALCULATE(AVERAGE(Sheet1[PH_Count]),

ALLEXCEPT(Sheet1,Sheet1[Company Number], Sheet1[Month])))

but the multiple rows for some months are skewing the average.

e.g. for company AW, the average should be 127 (apr) + 120 (may) + 120 (jun) + 127 (jul) + 122 (aug) + 126 (sept) = 742

divided by 6 months = 123.6666

I'm getting 124.30 because it's totalling all policyholders (1243) and dividing my number of rows (10).

The SUMX part of the dax is calculating correctly in the chart on the left (589.55 + 124.30 = 713.85), so i think i just need to sort out the CALCULATE(AVERAGE(Sheet1[PH_Count]) part of the dax.

here is a link to dl the pbix file. https://1drv.ms/u/s!AsBrid41P7C5hLUlqGOKV1ZtnWkODQ?e=gpLCvN 

Any help much appreciated, 

thanks

dan

[Anonymous]

thanks @amitchandak , do you mean to put that within the original measure like this? 

Total Policyholders = sumx(VALUES(Sheet1[Company Number]),

CALCULATE(AVERAGE(Sheet1[PH_Count]),      CALCULATE(AVERAGEX(values(sheet1[Month]) ,average([PH_Count])),

ALLEXCEPT(Sheet1,Sheet1[Company Number], Sheet1[Month])))

(i've had to tweak your dax to avg the ph_count because that isn't a measure)

however, i've tried it and i get the same results.

For the AW company, it's still averaging the 10 rows of data because it includes multiple rows for some of the months on account of the different products. I need it to average ph_count for the 6 months, ignoring the fact that some months have multiple rows.

thanks

dan

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

According to your description, I think you can modify your measure.

Like this:

Measure =
VAR a =
    SUMX (
        SUMMARIZE (
            Sheet1,
            [Company Name],
            [Month],
            "aa", AVERAGE ( Sheet1[PH_Count] )
        ),
        [aa]
    )
VAR b =
    DISTINCTCOUNT ( Sheet1[Month] )
RETURN
    DIVIDE ( a, b )

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[Anonymous]

Perfect, many thanks 🙂

[amitchandak]

@Anonymous , if PH count is a measure then try

AVERAGEX(values(sheet1[Month]) ,[PH_Count])