topic Re: Average of Rates in DAX Commands and Tips

Average of Rates

awashington6 — Sun, 27 Feb 2022 20:37:12 GMT

I built out a table in powerBI to give me average of a series or rates. However, the average is wrong. I wrote out the new table like this:

summarizecolumns(Sheet1[District],'Sheet1'[Fiscal Year],"Usage Rate",Average('Sheet1'[%Beds]))

For example, district only has one hospital with a rate of 81.1% for a specific year. However, when I create the table for with "Usage Rate" by district for that specific year, the table reads 83.5. The table should have a rate fo 81.1 for one hospital in that district for that year . Since that average is wrong, I can safely assume the average is wrong for all other districts with multiple hospitals.

Re: Average of Rates

littlemojopuppy — Sun, 27 Feb 2022 21:48:36 GMT

awashington6 can you share any data so someone can try to figure this out?

Re: Average of Rates

ValtteriN — Sun, 27 Feb 2022 22:15:58 GMT

Hi,

I think the likely reason behind this is that you have some filter affecting the calculation (in your test) or alternatively "However, when I create the table for with "Usage Rate" by district for that specific year, the table reads 83.5. "

Here you refer to year but in your original DAX you use fiscal year. I recommend double cheking filters + columns used since the DAX seems good on a glance. To elaborate further I would need to see the data. (as littemojopuppy mentioned)

Re: Average of Rates

awashington6 — Wed, 02 Mar 2022 13:13:31 GMT

I am still very new to this. I think I may have done this incorrect or described the situation incorrectly. I think I see what the problem is but do not know how to fix. I created a summarized table for totals by year. I then create another summarized table, create a matrix on the same page. It appears that the first matrix is threwing off the second matrix. In looking at the relationship, there is no relation between the two tables. However, they both are connected to same lookup table in relationshops. Any guidance would be helpful.

Re: Average of Rates

Anonymous — Thu, 03 Mar 2022 07:12:02 GMT

Hi awashington6 ,

Please try to modify you table.

an_other =summarizecolumns('Sheet1'[Fiscal Year],Sheet1[District],"Usage Rate",Average('Sheet1'[%Beds]))

If I have misunderstood your meaning, please provide your pbix file without privacy informarion and desird output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.