Category: Power BI

Matt Allington takes a pivot table and digs into components to help determine underlying drivers of change:

As you can see below, TV and Video total sales were up $178m vs prior year, yet there was also a decline in sales of $79m caused by lower sell prices.  And Cameras and camcorders actually had an increase in sales due to sell price, and that drove the total result higher than it otherwise would have been.  Of course there is normally an inverse relationship between price and volume (the lower the price, the more you sell).  The trick is to maximise sales (or more correctly margin $).

There are a lot of comments worth reading as well.

Dustin Ryan has a few more Power BI tips for us:

But what if I want to show a descriptive field as a tooltip? As of now, only measures can be displayed in the tooltip. But have no fear! One way to work around this is to create a custom calculation to display my descriptive field.

If I want to show a description of the classes within the subjects seen in the chart above, I can create a calculation like so:

Classes = CALCULATE(CONCATENATEX(VALUES(‘Grade data'[Class Name]),’Grade data'[Class Name],”, “))

So now I can show the classes within each subject as a tooltip

I really like his synonyms example.  Read the whole thing.

Reza Rad shares some tips on building mobile-friendly reports in Power BI:

Report fitted in my mobile screen, however when I see that in smart phone even with 5 inch screen, it is too small! texts are not readable in that size, and bar or column charts are too small to be selected with touch screen. When you design for smart phone size consider making sizes bigger. Also don’t use too many charts in one page, because it will make things small. few charts in each page will makes things readable and user will be able to highlight them and select items.

You can use formatting to make your font sizes bigger, and titles of charts bigger. However there are some charts and some elements that can’t be resized (for example labels inside tree map, or labels for x-axis in column chart below). Make sure to design big and clear with only few visualization elements in each page. Here is what I build and it shows in mobile phone nicely;

The upshot is that dashboards are about where we’d want mobile development to be—easy to use and “just works”—but reports have a ways to go yet.

Chris Webb shows how to create specific numerical and character sequences in Power BI:

It’s also possible to use this technique to create lists of characters. For example, the expression:

{“a”..”z”}

Returns a list containing all of the lowercase letters of the alphabet

There are a few interesting ways of generating sequences in M, some of them (as the first commenter notes) akin to Python’s sequence methods.

Reza Rad looks at how division can go wrong in Power Query:

In this example I just returned zero if I find error. but you can return error message if you like with [Revenue Per Item][ErrorMessage]. This method is great error handling method when an error out of blue happens in your data set. I always recommend using TRY method to get rid of errors that might stop the whole solution to work properly.

I have to mention that steps above are separated to show you how the output of try expression looks like. In fact you can combine both steps above in single step with TRY OTHERWISE as below (Thanks to Maxim Zelensky for pointing this out);

The end result is code which is a bit more complex, but safely handles a number of edge cases.

Reza Rad looks at using Power BI groups and integrating with Office 365:

When you share a content with an individual in the organization, if that person leave the company, or be replaced by someone else from another team, then you have to remove sharing from previous user account, and assign it to the new user account. Best practice is to share content with groups. and members of Groups then easily can be managed by an administrator. Power BI groups are fully synchronized with Office 365 groups. once you used a group in Power BI, then it is only an admin’s task to add/remove members from it.

I like this group-based approach a lot, as it makes dashboard security a lot easier.