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
paulvu0911
Frequent Visitor

Top 3 States in each Relationship and their percentages

Hi there,

 

I'm totally new to Power BI and really need help with a report I'm working on. I have a dataset that contains all loans originated within a relationship (Seller Buyer), and these loans are originated in different states. My goal is to display only top 3 states for each relationship, and show the percentage of these states in the total loan count in each relationship. I was able to choose the top 3 relationships with the highest loan counts for each relationship. However, I'm having issues turning these loan counts into percentage. I tried several ways but it shows the percentage of each state in the whole dataset, not the percentage in each relationship. Is there a way for me to do so in power BI? Thanks!

 

Top 3 States = 
VAR 
Rankingcontext = values('Flow Database2'[State])
Return
CALCULATE([Loan Count],TOPN(3, All('Flow Database2'[State]), [Loan Count]), Rankingcontext)/CALCULATE(sum('State Count'[Count]), KEEPFILTERS(values('State Count'[Seller Buyer])))
1 ACCEPTED SOLUTION

7 REPLIES 7
ChandeepChhabra
Impactful Individual
Impactful Individual

@paulvu0911 Can you please share some data and how should the expected output look like ?

Here is the simplified structured of the data.  I want to see for each Seller Buyer relationship, which are the top 3 states (by loan count), and the percentage of each state in the total of loan for each relationship. So for example, for Seller Buyer 1, CA, WA, and CO should be shown as the top 3 states, with their corresponding percentage (=2/7 = 28.6%). Hope it helps!

 

Seller Buyer Loan NoState
Seller Buyer 11CA
Seller Buyer 12CA
Seller Buyer 13WA
Seller Buyer 14CO
Seller Buyer 15GA
Seller Buyer 16CO
Seller Buyer 17WA
Seller Buyer 28TX
Seller Buyer 29FL
Seller Buyer 210TX
Seller Buyer 211NC

@paulvu0911 is this what you are looking for ?

 

Capture.PNG

 

Here is the pbix 

 

Thanks a million. That's exactly what I'm looking for. Just a follow up question. Is there a way to show rank the order of the table first by Seller Buyer, then by the percentage?

Capture.PNG

Is this how you want ?

 

Download pbix

@ChandeepChhabra: Is there a way to embed that RankX formula in the Top 3 state measure, so that we don't need a separate column for Rank by State?

@paulvu0911

You can concatenate both the formulas. I am not sure what you meant by embedding ?

 

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.