TopN Required

[jacobrcotton]

New Power BI user here, and this is my first project so please go easy on me!

I dont seem to be able to post my Relationships Map directly, so I've loaded them here for viewing: https://paste.pics/54GW8.

I have three FACTs (EnrollmentDetail, ClaimsLineDetail - Paid, ClaimsLineDetail - Incurred) that are all connected via 10ish dimensions (usually labeled as "[Dimension]Key").
*Note, specifically, that the three FACT files contain no uniqueness. IE, all field values can and do appear multiple times on the FACTs.
**The ClaimsLineDetail - Paid and ClaimsLineDetail - Incurred are identical in thier data, but connected to the DateTable via Paid Date and Incurred Date, respectively.

The Claims FACTs separate $ by Medical expenses and Pharmacy Expenses. Two of my measures "TotalPaid" & "Total Incurred" is simply the sum of these two fields via code:

[COLOR=#000000][FONT=Consolas]TotalPaid = CALCULATE(sum('ClaimLinesDetail - Paid'[Medical Paid])+sum('ClaimLinesDetail - Paid'[Pharmacy Paid]))
TotalIncurred = CALCULATE(sum('ClaimLinesDetail - Incurred'[Medical Paid])+sum('ClaimLinesDetail - Incurred'[Pharmacy Paid]))[/FONT][/COLOR]

I'm trying to
(1) Sum the Medical & Pharmacy into Total Paid (done)
(2) Sum the Total Paid by Member ID (done)
(3) Determine the TopN (2, 5, 10, whatever) Member IDs
(4) Sum the Total Paid of just those TopN Member IDs
(5) Divide the sum of (4) by theGrand Total Paid of all IDs.

To ID my TopN, my measure code is:

[COLOR=#000000][FONT=Consolas]Top2PaidTest001 = CALCULATE([TotalPaid], TOPN(2, MeasuresTable, [TotalPaid], DESC))[/FONT][/COLOR]

The results I get are clearly incorrect, but I'm not sure why. When I compare the Grand Total Paid against the Top2 Total Paid, I get the exact same number (~$10M, which is the correct amount for the Grand Total Paid).

 

[jacobrcotton]

A few corrections to the original post:

(3) Determine the TopN (2, 5, 10, whatever) Member ID based on Total Paid

I am happy to post my .pbix (de-IDd, of course), if that would help. Is that possible?

 

[gazpage]

What is MeasuresTable? Why would you want the two highest rows from that table?

 

[jacobrcotton]

(1) "MeasuresTable" is an empty table that i use to store my Measures value for organizational purposes. IE, it lets me find all my measures quickly & in 1 spot.

(2) I want the 2 (or 5, or 10, or whatever) highest because I would like to know how concentrated is my TotalPaid $, in the top 2, 5, 10, whatever number of members. So my first step is identify the Top N spenders and then calculate the % of the Grand Total Spend.

 

[gazpage]

Yeah, but your measure isn’t finding the top 2 spenders, it’s finding the top 2 measures on the measuretable. Look at the second parameter of your TOPN

 

[jacobrcotton]

Ok, so I guess I need my "TotalPaid" measure to actually be a column within the FACT table(s)? Again, my 1st project in Power BI, so if there are basic organizational issues, I'm open to revamping this.

 

[gazpage]

Well, no. Second clause of the topn just needs to be the table you want the top2 rows of.

 

[jacobrcotton]

oh man. I'm such an idiot. I was under the impression that the 2nd clause needed to be the table that contained the measure of the 3rd clause.

Your solution worked perfectly.

thank you so much!