Calculating averages based on week number by multiple locations

[ElvirBotic]

Hello, community, 

I am currently working on a small dashboard that I am building and one of the requirements is to show an avg. line through the line chart visual. Currently, I am trying to calculate the averages based on 5 criteria 1) a number of passed locations 2) Number of caution locations 3) a number of failed locations 4) week number 5) specific location. I want to focus on the "passed" avg for now. I need it to calculate based on a week of the year, multiple locations in that week, and the number of locations that passed. I will provide a screenshot of the model. Let me know if this makes sense. If not I will clarify further. Thanks for the HELP!

[amitchandak]

@ElvirBotic , Not very clear. Check if my WOW blog can help

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[ElvirBotic]

@amitchandak Okay, I will be clearer on what solution I am looking for. I need a measure created that will show me the average by week for each status column. Example would be week 2 the sum should be 164 for "Pass" column, 28 for "Caution" column, and 80 for "Fail" in week 2 alone, then go on to week 3. With that, I want to be able to show an average overall for each category. 

[v-kkf-msft · Accepted solution]

Hi @ElvirBotic ,

Try the following formula:

Avg = 
COALESCE(
  DIVIDE(
    SUM('Table'[Pass]),
    COUNT('Table'[Week])
  ),
  0
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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