Forum Discussion
percentages and multiple query columns
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!
- Greg_Deckler9 years agoCommunity Champion
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))