percentages and multiple query columns

[kincaids]

I'm new to Power BI and having a difficulty applying what I'm learning through the hours of tutorials to my particular situation, so your help is greatly appreciated.

Our company rents instruments to school children.  There are a three types of rental accounts:

• those that returned their instrument
• those that ended their agreement by owning their instrument (renting to ownership or paying off their balance early at a discount)
• those that are currently renting

Of the two (three) types of ended rentals, I want to determine what percentage returned their instrument, what percentage rented until ownership, and what percentage took the early pay-off discount.

Here's the infromation I have (both of these pictures represent columns found in the same query):

 Figure A   Figure B

Anyone who is still renting (A 'null' in the Return Date column and a 'P' in the rnt_dunn column) needs to not be taken into account for percentage purposes.

rental return customer - date in return column

owned through rental - null & X with Next Pmt # = Terms

owned through early pay-off - null & X with Next Pmt # does not = Terms

Using these columns/data, how would I get the answer I'm looking for?

[kincaids]

I'm guessing that this would be an ideal time for a drill down. The main visualization would be those returned versus those owned, while the drill down would occur once selecting "owned", revealing the percentage of early payoff's versus those who rented all the way to ownership.

[kincaids]

*bump*

[Greg_Deckler]

@kincaids - To get this question answered, you need to post some sample data that is not a picture and then the result that you would expect from that sample data. That way it is easy to recreate your issue and find a solution.

[kincaids]

I know these are pictures, but I hope this is what you're looking for.

currently renting - 7/15

returns - 4/15 (return dates)

own instrument - 4/15 (null & X)

of the owned instruments:

rented until owned - 1/15 (row 4 - "next pmt #" = "terms")

took early pay-off discount - 3/15 (rows 7, 9, 10 - "nxt pmt #" < "terms")

So, I would hope to see in this data that 46.66% are currently renting, 26.67% have returned their instrument, and 26.67 own their instrument.

When drilling down into those that own their instrument, I would expect to see that 25% rented until they owned and 75% took the early pay-off discount (or that 6.67% of all renters rented until they owned and 20% of all renters take the early pay-off discount).  I hope this helps.

[Greg_Deckler]

In the future, I generally look for data pasted like so:

Return Daternt_dunnNext Pmt #Terms (Months)

6/2/2014	P	8	37
	P	10	63
	P	15	20
	X	19	19
	P	21	63
	P	15	36
	X	35	39
	P	3	37
	X	32	34
	X	30	35
	P	3	34
	P	36	63
12/24/2013	P	1	37
12/4/2014	P	3	36
5/28/2015	P	7	32

Makes it much, much easier to recreate an issue/scenario rather than having to type in all of the data manually.

Here is the Enter Data query I used to recreate your scenario:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBLDsAgCETvwtpEBfz0Fl02Md7/GsVflKYLZvGGiYOlQLRo0XkGA7dMlqEE1RSYxDuRSAoFEXQLPQ1dQ44t9P9BimeQOlJBUh36EjZhhdwIf3OsSNwVvNzJ7VRaZfYz4vH5C7RrBou5WWFaadSp9QU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Return Date" = _t, rnt_dunn = _t, #"Next Pmt #" = _t, #"Terms (Months)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Return Date", type date}, {"rnt_dunn", type text}, {"Next Pmt #", Int64.Type}, {"Terms (Months)", Int64.Type}})
in
    #"Changed Type"

Here is the first measure, % Renters:

% Renting = CALCULATE(COUNTROWS(Renters),Renters[Return Date]=BLANK(),Renters[rnt_dunn]="P")/COUNTROWS(ALL(Renters))

% Returns

% Returns = CALCULATE(COUNTROWS(Renters),Renters[Return Date]<>BLANK())/COUNTROWS(ALL(Renters))

% Owners

% Owners = CALCULATE(COUNTROWS(Renters),Renters[rnt_dunn]="X")/COUNTROWS(ALL(Renters))

For the next one, create a column:

Payoff = IF([Next Pmt #]<[Terms (Months)],1,0)

And then a Measure:

% Rented Until Owned = CALCULATE(COUNTROWS(Renters),Renters[Payoff]=0,Renters[rnt_dunn]="X")/CALCULATE(COUNTROWS(Renters),Renters[rnt_dunn]="X")

And a Measure:

% Early Payoff = CALCULATE(COUNTROWS(Renters),Renters[Payoff]=1,Renters[rnt_dunn]="X")/CALCULATE(COUNTROWS(Renters),Renters[rnt_dunn]="X")

Should be able to figure out any other calculations that you need from these examples.

[kincaids]

@Greg_Deckler

Ok, I think I'm starting to figure some of this out on my own, but still need help.  I think what I needed to do was take your

% Owners = CALCULATE(COUNTROWS('Rental Accounts - scrental'),'Rental Accounts - scrental'[rnt_dunn]="X")/COUNTROWS(ALL('Rental Accounts - scrental'))

and change it to

Owned = CALCULATE(COUNTROWS('Rental Accounts - scrental'),'Rental Accounts - scrental'[rnt_dunn]="X")

I want to do the same with the "% Renters" formula, but I realized I made a slight mistake.  How would I make the formula say that Renters are all that have 'null' as a return date and a 'P' OR AN 'N' in the "rnt_dunn" column?  I've tried a few different things, but I can't seem to get the syntax correct.

This has been a great exercise for me...learning a lot!

[Greg_Deckler]

I believe it would either be:

% Renting = CALCULATE(COUNTROWS(Renters),Renters[Return Date]=BLANK(),Renters[rnt_dunn]="P" || Renters[rnt_dunn]="N")/COUNTROWS(ALL(Renters))

or

% Renting = CALCULATE(COUNTROWS(Renters),Renters[Return Date]=BLANK(),Renters[rnt_dunn]="P" | Renters[rnt_dunn]="N")/COUNTROWS(ALL(Renters))

[kincaids]

Wow...thanks for this!  I wasn't sure how to create a table like that in a post, but it dawned on me that it could be described in the community blog post you sent me, and of course it was, so hopefully I won't make that mistake in the future.

I'm starting to see data, but I'm getting hung up a bit.  First, I'm recreating the 'test', seeing that it works there before applying this to the 'real', more involved table.  I've completed all the steps and this is what I've come up with:

If I unselect the "% Renting" ("Renting" in the pictures - I took out the % sign), the pie chart changes appropirately to reflect that now 50% are Returns and 50% are Owners.

Here are my issues:

1. When I mouse over the pie chart as you see it here, I'm getting "Renting - 0.47 (46.67%)" when I'm hoping to see "Renting - 7 (46.67%)"
2. I can't figure out how to drill down in this case.  Where should the new measures for "% Rented Until Owned" & "% Early Payoff" go so that when I click on "Owners", I see a new chart showing 25% Rented Until Owned and 75% Early Payoff?  I don't even see the drill down icon above the pie chart when it's selected.

I'm wondering if I've worded my overarching question wrong.  I think I need to start with counts - in other words, how many total renters are there?  How many Returns are there?  How many Owners are there?  When I put them in a pie chart, it should automatically create percentages, I would think.  That way, later on I can ask other similar questions from the same counts.  Hopefully I'm making sense.  Thanks again for your help on this and your patience with me!  I'm learning a lot!

[v-huizhn-msft]

Hi @kincaids,

>>I'm getting "Renting - 0.47 (46.67%)" when I'm hoping to see "Renting - 7 (46.67%)"

The first -0.47 is round if 46.67%. "Renting - 7 (46.67%)", what's the mean of -7?

>> Where should the new measures for "% Rented Until Owned" & "% Early Payoff" go so that when I click on "Owners", I see a new chart showing 25% Rented Until Owned and 75% Early Payoff?  I don't even see the drill down icon above the pie chart when it's selected.

We can create drill down in Pie Chart by select multiple fileds in Legend level.  While 25% Rented Until Owned and 75% Early Payoff  are calculated by measure, the measures are not able to add Legend level.

Best Regards,
Angelia

[kincaids]

---

---

The first -0.47 is round if 46.67%. "Renting - 7 (46.67%)", what's the mean of -7?

It is from the example found further up in the thread.

---

---

We can create drill down in Pie Chart by select multiple fileds in Legend level.  While 25% Rented Until Owned and 75% Early Payoff  are calculated by measure, the measures are not able to add Legend level.

I'm not sure I'm following you here...could you expound on this?  Thanks!