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.
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.
