Forum Discussion

kincaids's avatar
kincaids
Helper II
9 years ago

percentages and multiple query columns

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 instrument...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

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's avatar
kincaids
Helper II
9 years ago

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's avatar
    Greg_Deckler
    Community Champion
    9 years ago

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

     

    Return Daternt_dunnNext Pmt #Terms (Months)

    6/2/2014P837
     P1063
     P1520
     X1919
     P2163
     P1536
     X3539
     P337
     X3234
     X3035
     P334
     P3663
    12/24/2013P137
    12/4/2014P336
    5/28/2015P732

     

    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's avatar
      kincaids
      Helper II
      9 years ago

      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's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        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's avatar
      kincaids
      Helper II
      9 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        9 years ago

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