Power BI Desktop May 2020 Feature Update

Good things come to those who wait. The Power BI Desktop May 2020 Feature Summary this week may have come a bit late in the month, but it is packed full of functionality. A full list of this month's updates can be found on the Power BI blog (link above), but I want to highlight a few of the Reporting features in this post today:

Reporting

• Drill through button action is now generally available
• Conditionally set the drill through destination
• Conditionally format disabled and enabled state tooltips
• Customize formatting for the disabled state
• Enhancements to page navigation action
• Conditionally set the navigation destination
• Support for shapes and images
• Buttons now support fill images
• Drop shadow support for visuals

I have been busy working on writing a new Microsoft 365 Overview course and these reporting features fit in perfectly with what I've been cooking up. Here's a preview of what the course will entail: 

This month's reporting update is all about the buttons, and as I've hinted, it could not have come at a better time. Just last week I was spending hours trying to fix this: 

It should be so simple to tile the text and the icon within a button in order to avoid that ugly overlap, but Power BI formatting does not yet allow it (and still does not in the May update). 

Of course we can get around this in a few ways: 

• split the above into two components - text and image
• remove the arrow image
• use an image as the button

Unfortunately these options all previously limited the inherent capability of buttons to change formatting based on their state (default, hover, press, disabled) or just simply didn't look as nice. For those of us who like to get the appearance spot on, that's a big limitation. 

With May's update, we can get the best of both worlds. We can now use an image as a fill for buttons and still keep our conditional state formatting. This means we can pull through beautifully formatted images, buttons, icons and graphics and use them in Power BI. 

Before the new updates I was stuck with the limited icons in Power BI buttons: 

After the new release, I can now spend hours dreaming up any button design I want. Sometimes it really is the simple things in life. A picture can speak a thousand words and in our global environment it is helpful to have a report that can be easily understood in any language. If we can leverage the implied meaning behind well-known icons, it will save us a lot of trouble training people on how to consume and interact with the report. We all know what the X means in the picture below, and an eraser indicates clear filters far better than an undo arrow: 

If we can make our report intuitive, the insights and information gathered from the data will come easily. If the report is too hard to use, the data insights risk being lost in translation. Which brings me back to that Drillthrough button I spent hours trying to resolve. I have been LOVING the drillthrough button because it makes a really valuable feature much more intuitive. But of course, it still has to look good. I got so excited when I learned I could use images inside my buttons, thinking I could try something like this as a filled image: 

Upon further investigation into the new features, a bit more experimentation with my report layout, and in an effort to keep things as intuitive as possible I decided to scrap the images for this one and expand the button to match the width of the visual to which it's connected. This gave me more space for the text (which I will conditionally format) and made it clearer what the button related to. Now, a user simply has to click on an item in the treemap and then the drillthough button will activate and encourage them to view the drillthrough page. I can't tell you how excited I am about this! I have loved playing with it in preview mode and am so glad to see it as a standard feature. 

For those of you who are not familiar with drillthrough, you're not alone, and that's what makes this button so valuable. Drillthrough is a fantastic feature which enables a user to select one or more filters, categories, and data points, and then 'drillthrough' to another page. That page will contain all the selected filters and can display any visuals and information the report designer felt was necessary. It can be a great way to start with a summary page and drillthrough to more detailed analysis and information. But it was previously hidden away in the right click menu. Not too long ago Microsoft released an update that made the drillthrough capability a bit more obvious by adding a note to the tooltip, but this still wasn't exactly intuitive and often required a bit of training to let users know what to do. 

Now, the drillthrough button sits in a disabled state on the page and can be conditionally formatted to say anything you can dream up with DAX. In the sample above I've kept it really simple and just changed the button to use the name of the selected app. I have opted to still use the 'drillthrough' terminology as this is for a Microsoft 365 Overview course and I think getting up to speed with the correct lingo is important to learning, but it's not necessary in all cases so with the new drillthrough button you can write any message you want that makes sense to you and your report viewers. 

We're not done with buttons yet. Page navigation has been a readily available button action since March. You may think this is unnecessary when Power BI reports have page navigation built in to them, but then we could say the same for slicers when the filter pane is readily available. What I like about page navigation BUTTONS is that they are CONSISTENT. I share reports with colleagues, students and general public. This means my report viewers are accessing the report through embedded web iframes, Teams, Power BI Service, Power BI mobile app, and even Power BI desktop from time to time. The built in page navigation in Power BI changes from platform to platform. 

Despite liking the idea, I didn't use it often because it can take up a lot of space: 

Now we have the ability to create a Table of Contents table in our data model that matches the page names and use the slicers in combination with DAX and Buttons to create a space saving yet consistent page navigation experience across all platforms. 

And just for fun, throw a few shadows on your visuals to make them stand out. Can you find them in the page navigation demo above? I'll have to play around with that one a bit more to see where and how it adds most value. I'm a big believer in less is more, but sometimes a bit of emphasis comes in handy when used sparingly.

To recap, I have highlighted the May 2020 Reporting Features updates that have brought a few great preview features into regular production and made it easier to: 

• build an intuitive report using meaningful icons that translate into any language
• highlight drillthrough functionality
• provide a consistent page navigation experience for users across all platforms
• accent key information with the use of drop shadows

Enjoy the new features and let me know your thoughts on them. 

Calculating Percentile

How do you calculate percentile using DAX?

Let's start by defining percentile. The Oxford Dictionary defines it as:

one of the 100 equal groups that a larger group of people can be divided into, according to their place on a scale measuring a particular value

In statistics we define percentile as the percentage of values in a given set which fall below the selected value. This can be represented mathematically using the below formula:

P = (N-R)/N

Where:

N = the total count of values in the given set

R = the rank of the selected value compared to the given set in DESCENDING order

P = the percentile of the selected value in the given set

I have emphasized some of the key words here that will help us translate this definition into DAX. 

Step 1: N = the total count of values in the given set

We know we need to count the number of values in the set, for this we can use the COUNTROWS() function in DAX. However, in order to get total anything in DAX, we need to use a filter expression such as ALLSELECTED() to ignore any row context in the table or visuals we are using, but still honour the filter context of the slicers and filters we use. 

For each and every value in the given set, I want to return the same count, but I want that count to update with filters.

So if we have a sample population as given below, the total count of values is 5:

Gender	Value	N
Male	80	5
Female	78	5
Male	80	5
Female	74	5
Female	90	5

But when we filter for Males only, the total count of values becomes 2:

Gender	Value	N
Male	80	2
Male	80	2

We therefore define N as follows:

N= COUNTROWS(ALLSELECTED(GivenSet))

Step 2: R = the rank of the selected value compared to the given set in DESCENDING order

There are a few ways to calculate rankings in DAX, but we already discussed how I want this calculation to be dynamic and update based on slicer and filter selections. 

So if we consider the same population as given from above, the rank for the total population would be as follows, putting a Female in the highest rank of 1:

Gender	Value	N	R
Male	80	5	2
Female	78	5	3
Male	78	5	3
Female	74	5	5
Female	90	5	1

But when we filter for Males only, the highest ranked value belongs to a male:

Gender	Value	N	R
Male	80	2	1
Male	78	2	2

Therefore we need to use ALLSELECTED again, and also an RANKX function in DESCending order here:

R = RANKX(ALLSELECTED(GivenSet), CALCULATE(SUM(GivenSet[Value])), , ASC)

Step 3: P = the percentile of the selected value in the given set

That leaves us with the simple task of dividing our two values, so we will use the DIVIDE() function to avoid errors. 

From above, we know that: 

P = (N-R)/N

P = DIVIDE(N-R, N)

To save us having to copy the DAX for each, we can simply reuse the measures we already calculated for N and R. Because they are MEASURES, they will calculate within the Row and Filter context of our slicers and matrix. 

P = DIVIDE ([N]-[R] ,

    [N]

)

Sticking with our same sample set, we will get the below results for the entire population. Note our highest ranked male is in the 60th percentile (60% of the sample performed lower than he did): 

Gender	Value	N	R	N-R	P
Male	80	5	2	3	60%
Female	78	5	3	2	40%
Male	78	5	3	2	40%
Female	74	5	5	0	0%
Female	90	5	1	4	80%

But when we filter for Males only, the highest ranked male now drops to the 50th percentile (only 50% of the sample performed lower than he did):

Gender	Value	N	R	N-R	P
Male	80	2	1	1	50%
Male	78	2	2	0	0%

Real World Example: Birth Weight Percentile

All right, so let's see this in practical use. 

A common use of percentile is for comparing children's growth from birth. You have probably seen one of these growth charts in a doctor's office at some point in your life: 

CDC Growth Chart - Boys

What does it actually mean? Looking at birth weight for boys, according to this chart, the following percentiles apply: 

Approx. Birth Weight (g)	Percentile
2500	5
2800	10
3200	25
3600	50
3900	75
4200	80
4300	95

So that means, on average 25% of babies born are less than 3200g at birth. 

I have compiled birth statistics from 2018 in the US, and used the above method for calculating N, R and P according to birth weight. 

N =

COUNTROWS (

    ALLSELECTED ( '2018 Births US'[Sex of Infant], '2018 Births US'[Index] )

)

R =

RANKX (

    ALLSELECTED ( '2018 Births US'[Index] ),

    CALCULATE ( AVERAGE ( '2018 Births US'[Birth Weight - Detail in Grams] ) ),

    ,

    DESC

)

Birth Weight Percentile =

DIVIDE ( [N] - [R], [N] )

Now we can see if these average percentiles apply to smaller sample sizes of the population, or if, for example, babies born in January to fathers with a Doctorate degree, fall above or below this average: