Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
leobelyaev
Frequent Visitor

Calculating average daily count from counting rows?

Hello,

 

New user.

I am not sure how to approach the project, since I am usnsure how to 'nest' functions in Power BI DAX or if that's even necessary.

 

Here's what My Dataset looks like:

screenshot.png

Each row represents an encounter.

My final result should be a dashboard that displays the average number of visits by DayOfWeek  (Monday, Tuesday etc.)

Ideally it needs to be able to show data at different levels. Either using drill through, toggle, slicer or have different tables showing:

 

Avg. number of visits by

Provider(VISIT_PROV_NAME),

Encounter Type(ENC_TYPE_TITLE),

Facility (FAC_ID)

modality (prc_modality).

 

I am not sure how to tackle this assignment in Power BI, but here are some steps I took to query for the same data in SQL, wonder if any of that logic carries over or helps at all.

 

1.Counted and summed up number of encounters by APPT_Date (by date), to get Daily Encounters

2. Got the average for Daily Encounters

3.Filtered by DayOfWeek, Provider, Enounter type, Facility, Modality.

 

I am now wondering if it is possible, and how hard it would be to perform the same process in Power BI, though I am very new.

 

Any help on how to approach this would be greatly appreciated!

 

Any suggestions on good tutorials etc. would be appreciated too!

 

Thanks!

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@leobelyaev , Avg Daily would be like

 

Averagex(Values(Date[Date]), calculate(Count(Table[Column])) )

 

If you want to switch with the level you can use isinscope

 

Avg of Sum : https://youtu.be/cN8AO3_vmlY?t=22980

 

Isinscope

https://youtu.be/cN8AO3_vmlY?t=31995

IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@leobelyaev , Avg Daily would be like

 

Averagex(Values(Date[Date]), calculate(Count(Table[Column])) )

 

If you want to switch with the level you can use isinscope

 

Avg of Sum : https://youtu.be/cN8AO3_vmlY?t=22980

 

Isinscope

https://youtu.be/cN8AO3_vmlY?t=31995

IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi, thank you so much Amit Chandak,

 

That helped a lot, I checked out your tutorial and used the code you sent.

 

However, I get very different results when using the formula you suggested, and when i tried to copy the forumla from your tutorial. I can't quite figure out why and how the results are so different from using seemingly similar logic behind the two formulas.

 

 

Do you think you could explain how this could be happening? If you could, you'd be better than ChatGPT, because it failed to explain to me hahah.

screenshot2.png

The first matrix uses this measure: 

Average visit = AVERAGEX(SUMMARIZE(PRC,PRC[APPT_DATE].[Date]),COUNT(PRC[ENC_TYPE_TITLE]))
 
The second uses this measure:
avg = AVERAGEX(Values(PRC[APPT_DATE]),CALCULATE(COUNT(PRC[ENC_TYPE_TITLE])))



 

 

By the way, planning to watch the rest of your 11.5 hour beginner course, you are great at explaining concepts, hands on, with examples, step by step! I really liked what I saw so far!

 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.