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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Nsalvi
Frequent Visitor

MIN, MAX, AVERAGE, PERCENTILE of Count

Hi,
I am new to statistical summary and want to calculate MIN, MAX, AVERAGE, PERCENTILE of count of patients visits using DAX.  This will help me to show the distribution of visits by Patients, Doctors, Occupation and Visit Status, Visit Type and Day of the week. I already have a measure for count of visits which is basically COUNTROWS of the fact table "Visits". As I understand, MIN, MAX does not accept DAX measure and  therefore I am stuck.

Following is the sample of my fact table "Visits".

Nsalvi_1-1713196715741.png

 

Please help!!

1 ACCEPTED SOLUTION

Hi @Nsalvi ,

 

I modified my dax based on your description. please point out if I misunderstood you.

Count of Visits = COUNTA('Table'[Visit_Type_ID])
Min_visits = MINX(SUMMARIZE(ALLSELECTED('Table'),[Patient MRN],"visits",[Count of Visits]),[visits])
Max_visits = MAXX(SUMMARIZE(ALLSELECTED('Table'),[Patient Account Number],"visits",[Count of Visits]),[visits])
Avg_visits = 
var _a= SUMX(SUMMARIZE(ALLSELECTED('Table'),'Table'[Patient MRN],"visit",[Count of Visits]),[visit])
var _b= CALCULATE(DISTINCTCOUNT('Table'[Patient MRN]),ALLSELECTED('Table'))
RETURN DIVIDE(_a,_b,0)
Column = COUNTROWS(FILTER('Table','Table'[Patient MRN]=EARLIER('Table'[Patient MRN])))
PERCENTILE.INC 0.25 = PERCENTILE.INC ([Column],0.25)
VisitsPercentile 25 = CALCULATE([PERCENTILE.INC 0.25],ALLSELECTED('Table'))

vtangjiemsft_0-1713496935217.png

 

Best Regards,

Neeko Tang

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

 

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result in a simple Table format.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur ,

Please find below sample  data in table format:

IndexDate Of ServicePatient Account NumberPatient MRNAppt_P_IDVisit_Status_IDVisit_Type_ID
11/2/201812060611044111
21/2/20186879711052221
31/2/20187367111064221
41/2/201811997411094322
51/2/20186690411096422
61/2/20186690411096521
71/2/20182967911128621
81/2/201811918311145521
91/2/20186120211213221
101/2/20186842911247221
111/2/201811819211284521
121/2/20186943511308621
131/2/20183077811319221
141/2/20186002911335521
161/2/201811271411347322
151/2/201811271411347221
171/2/201812258711405521
181/2/201811889411484621
191/2/201811862311492221
201/2/20186251311532221


Following is the sample expected result:

Patient MRNTotal VisitsMin VisitsMax VisitsAverage VisitsPercentile 25
11044     
11052     
11064     
11094     
11096     
11096     
11128     
11145     
11213     
11247     
11284     
11308     
11319     
11335     
11347     
11347     
11405     
11484     
11492     
11532     


I would like to see the expected results by selecting any of the following categories:

Date of Service (Selected Range)
Patient MRN
Appt_P_ID
Visit_Status_ID
Visit_Type_ID

I hope this clarifies the matter.

Thank you for your help!

Hi,

What do you mean by min visits/max visits?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur ,

 

What I mean by min visits and max visits is that what is the minimum count of patients visits to the clinic and what was the maximum count of patients visits to the clinc.

I hope that helps!

v-tangjie-msft
Community Support
Community Support

Hi @Nsalvi ,

 

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

vtangjiemsft_0-1713318907682.png

(2) We can create a calculated column.

Column = COUNTROWS(FILTER('Table','Table'[Patient Account Number]=EARLIER('Table'[Patient Account Number])))

(3) We can create measures. 

Visits = COUNTA('Table'[Visit_Type_ID])
Min_visits = MINX(SUMMARIZE(ALLSELECTED('Table'),[Patient Account Number],"visits",[Visits]),[visits])
Max_visits = MAXX(SUMMARIZE(ALLSELECTED('Table'),[Patient Account Number],"visits",[Visits]),[visits])
Avg_visits = DIVIDE(SUMX(VALUES('Table'[Patient Account Number]),[Visits]),DISTINCTCOUNT('Table'[Patient Account Number]),0)
PERCENTILE.INC 0.25 = PERCENTILE.INC ([Column],0.25)

(4) Then the result is as follows.

vtangjiemsft_1-1713319288011.png

 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

 

Best Regards,

Neeko Tang

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

Hi @v-tangjie-msft ,

Thank you for your reply with solution.

However, after creating calculated column and all the measures, I am getting following results which is incorrect. 

The only change I have made in your measures is that I have used "Patient MRN" instead of "Patient Account Number" column.

Nsalvi_0-1713428955044.png

Please help to understand why am I getting the above results.

Thank you!!


 

Hi @Nsalvi ,

 

I modified my dax based on your description. please point out if I misunderstood you.

Count of Visits = COUNTA('Table'[Visit_Type_ID])
Min_visits = MINX(SUMMARIZE(ALLSELECTED('Table'),[Patient MRN],"visits",[Count of Visits]),[visits])
Max_visits = MAXX(SUMMARIZE(ALLSELECTED('Table'),[Patient Account Number],"visits",[Count of Visits]),[visits])
Avg_visits = 
var _a= SUMX(SUMMARIZE(ALLSELECTED('Table'),'Table'[Patient MRN],"visit",[Count of Visits]),[visit])
var _b= CALCULATE(DISTINCTCOUNT('Table'[Patient MRN]),ALLSELECTED('Table'))
RETURN DIVIDE(_a,_b,0)
Column = COUNTROWS(FILTER('Table','Table'[Patient MRN]=EARLIER('Table'[Patient MRN])))
PERCENTILE.INC 0.25 = PERCENTILE.INC ([Column],0.25)
VisitsPercentile 25 = CALCULATE([PERCENTILE.INC 0.25],ALLSELECTED('Table'))

vtangjiemsft_0-1713496935217.png

 

Best Regards,

Neeko Tang

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

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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