Forum Discussion

awashington6's avatar
awashington6
Regular Visitor
4 years ago

Average of Rates

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. 

 

 

average
Average Averages
Average Rates
New Table

4 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    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)

  • awashington6's avatar
    awashington6
    Regular Visitor
    4 years ago

    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.     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.