Forum Discussion
percentages and multiple query columns
*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.
- kincaids9 years agoHelper II
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_Deckler9 years agoCommunity Champion
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.
- kincaids9 years agoHelper II
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:
- 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%)"
- 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!