Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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:
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):
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?
*bump*
@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.
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.
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.
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!
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))
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:
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!
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
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!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
58 | |
57 |