Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
Solved! Go to 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
thanks @amitchandak , do you mean to put that within the original measure like this?
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
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
Perfect, many thanks 🙂
@Anonymous , if PH count is a measure then try
AVERAGEX(values(sheet1[Month]) ,[PH_Count])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |