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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Benchmarking medical insurance claims data by multiple dimensions

Hi,

 

New user, love the product! I have quite a complicated question (for a beginner) but I am sure it will be easy enough for one of you to solve. 

 

I am analysing medical insurance claims data submitted by practices all over South Africa. I have 1 Fact table and 3 Dimension tables: Claims Fact, Date Dimension, Patient Dimension and Practice Dimension. The Data Model is depicted below:

 Data ModelData ModelDon’t worry about the other tables out of the picture for now. I want 2 slicers. The first should list all the Practice Nos (from the Practice Dimension), The second should have 2 benchmarks - 'Province' and 'National'. There will be two charts, most likely Dot Plots - haven't decided yet, but the format doesn’t really matter for now.

 

The first chart will have the month (from the Date Dimension) running along its axis and display the average number of patients seen for that particular month for the selected Practice No. The second chart will again have the month (from the Date Dimension) running along its axis and display the average claim amount (from the Claim Fact table) per patient for that particular month for the selected Practice No.

 

Easy enough so far but now it gets a little complicated. I want to give the user the option to benchmark their selected Practice No against all other practices operating in the same province as well as nationally. To do this, the user will select their chosen benchmark option from the second slicer mentioned above. However, whichever benchmark is chosen, that Practice No's values should not be included in the chosen benchmark.

 

For example, taking the ave number of patients seen case. If the user selects to benchmark the Practice No by province, the ave number of patients seen in the same province as the selected practice should be displayed excluding the selected Practice No's values. Same if the user wants to benchmark the Practice No nationally. The national benchmark should be calculated excluding the selected Practice No's values.

 

The situation gets slightly more complex if the user selects to benchmark both by province and nationally. In this case the provincial benchmark should be calculated without the selected Practice No's values and the national benchmark should be calculated without the province and the selected Practice Nos values.

 

In Summary:

 

Practice             Province            Province Benchmark

Practice A          Western Cape     Ave no of patients for all practices in the Western Cape excluding Practice A

                                                     National Benchmark

                                                     Ave no of patients for all practices in South Africa excluding Practice A

                                                     Province and National Benchmark

                                                     Ave no of patients for all practices in the Western Cape and then nationally excluding Practice A and the Western Cape

 

And then same logic needs to be applied to the other chart which displays the ave claim amount per patient. Shooo bit of a head twister, I know. Any help would be greatly appreciated.

 

Kind Regards,

Paul

 

2 ACCEPTED SOLUTIONS
vanessafvg
Super User
Super User

@Anonymous paul this is quite a good video to watch if you want to use a slicer to move between meaures, some "lekker" tips in here too

 

https://youtu.be/Mpk9Js5jUeg

 

this starts at minute 11 ish





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

Anonymous
Not applicable

@vanessafvg thanks for the tip. Will certainly give it a look.

View solution in original post

4 REPLIES 4
vanessafvg
Super User
Super User

@Anonymous paul this is quite a good video to watch if you want to use a slicer to move between meaures, some "lekker" tips in here too

 

https://youtu.be/Mpk9Js5jUeg

 

this starts at minute 11 ish





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

@vanessafvg thanks for the tip. Will certainly give it a look.

erik_tarnvik
Solution Specialist
Solution Specialist

You should be able to detect the precense of a filter on Practice and/or Province, using for example HASONEVALUE(), and define your benchmark formulas to exclude the values for those dimensions when calculating your averages. You may need to your share your benchmark measures to get help with that.

Anonymous
Not applicable

Thanks for the input. I will have a look and revert.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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