Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kincaids
Helper II
Helper II

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 instruments to school children.  There are a three types of rental accounts:

 

  • those that returned their instrument
  • those that ended their agreement by owning their instrument (renting to ownership or paying off their balance early at a discount)
  • those that are currently renting

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

 

 Figure AFigure A   Figure BFigure B

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?

10 REPLIES 10
kincaids
Helper II
Helper II

I'm guessing that this would be an ideal time for a drill down. The main visualization would be those returned versus those owned, while the drill down would occur once selecting "owned", revealing the percentage of early payoff's versus those who rented all the way to ownership.

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

I know these are pictures, but I hope this is what you're looking for.

 

test.png

 

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

 

 

 

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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!

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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:

 

table.pngchart.png

Viz_Fields.png

 

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!

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!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.