cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
paulvu0911 Frequent Visitor
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

Accepted Solutions
ChandeepChhabra Established Member
Established Member

Re: Top 3 States in each Relationship and their percentages

Capture.PNG

Is this how you want ?

 

Download pbix

7 REPLIES 7
ChandeepChhabra Established Member
Established Member

Re: Top 3 States in each Relationship and their percentages

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

paulvu0911 Frequent Visitor
Frequent Visitor

Re: Top 3 States in each Relationship and their percentages

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
ChandeepChhabra Established Member
Established Member

Re: Top 3 States in each Relationship and their percentages

@paulvu0911 is this what you are looking for ?

 

Capture.PNG

 

Here is the pbix 

 

Highlighted
paulvu0911 Frequent Visitor
Frequent Visitor

Re: Top 3 States in each Relationship and their percentages

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?

ChandeepChhabra Established Member
Established Member

Re: Top 3 States in each Relationship and their percentages

Capture.PNG

Is this how you want ?

 

Download pbix

paulvu0911 Frequent Visitor
Frequent Visitor

Re: Top 3 States in each Relationship and their percentages

@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?

ChandeepChhabra Established Member
Established Member

Re: Top 3 States in each Relationship and their percentages

@paulvu0911

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