Press "Enter" to skip to content

Category: Power BI

Building a P&L Statement with Power BI

Published 2020-04-29 by Kevin Feasel

Matt Allington has the need for some financials:

A few weeks ago, Danielle Stein Fairhurst from Plum Solutions contacted me and asked if I would speak at her financial modelling meetup group (a topic of my choice related to Power BI). I decided to speak about creating a P&L using Power BI and DAX. I first learnt how to do this way back in 2015 when I met Derek Rickard. I was still fairly early on my DAX journey at the time and certainly didn’t have any experience with financial accounting using DAX. Derek shared with me how he built his P&L using Adventure Works. I have used these principles many times to help customers, and I thought it would be a great time to pass on that knowledge, and the skills I have learnt along the way on my blog.

Click through for a video and the measures used.

Comments closed

Aggregations in Power BI Desktop

Published 2020-04-27 by Kevin Feasel

Jeroen ter Heerdt tries out aggregations in Power BI Desktop:

Aggregations bring me back to the good old SSAS Multidimensional days. The days that I invariably built the aggregations tree the wrong way around, SSAS would complain, I would scream and eventually give in.

You can imagine that I was curious but skeptical when I tried aggregations in Power BI Desktop. I was afraid of ending up in the same hate-but-need relationship that I had with SSAS multidimensional when it came to aggregations.

The good news? It is not like that. At all. Once you have aggregations working, they are great.

The not so good news? It took me longer than I am willing to admit getting them working – primarily due to data types and creation of the aggregated table. More details in this blog post.

Read on for the full story.

Comments closed

Sentiment Analysis with Power BI

Published 2020-04-27 by Kevin Feasel

Teo Lachev takes us through two options available for sentiment analysis with Power BI:

Cognitive Services is an Azure PaaS cloud service that supports text analytics and image recognition. It’s automatically included in Power BI Premium or Embedded capacities (make sure that AI workloads are enabled in the capacity settings). If you organization doesn’t have Power BI Premium or Embedded, you can provision Cognitive Services in Azure (requires an Azure subscription) and then write a custom Power Query function to invoke its APIs, as demonstrated by this tutorial. If you provision Cognitive Services outside Power BI Premium,  you’ll be charged per transaction. In the case of Power BI, the number of transactions equates to the number of rows in your table. So, if you refresh five times a table with 1,000 rows and calculate the sentiment polarity score for each row, you’ll be charged for 5,000 transactions.

Read on for the full report on each option.

Comments closed

Moving Queries to and from Power BI Desktop and Excel

Published 2020-04-24 by Kevin Feasel

Erik Svensen shows how we can copy queries from Power BI’s Power Query Editor into Excel and back:

Today I discovered a way to easily copy all queries (including the groups) from a PBIX file to Excel without entering the power query editor in Excel.

In my case I needed to copy the data returned by a query and find it some much easier to get the data using Excel instead of generating a csv file from a visual in Power BI Desktop and importing that into Excel.

Click through for a step-by-step demonstration of the process.

Comments closed

Dynamic String Formats for Power BI Calculation Groups

Published 2020-04-23 by Kevin Feasel

Kasper de Jonge shows how we can build out different format strings based on calculation groups in Power BI:

One of the cool things of calculation groups is they cannot just be used to apply a calculation over your “base measure” but also apply a dynamic formatstring. You could do this without calculation groups before too as I described here. This method had one big drawback though, it uses the FORMAT function and when doing that all results are transformed into strings. This does help in showing the right format, but you lose a lot of other functionality like sorting or conditional access. Now with calculation groups we can do custom formatting and keep the data type. How does that work?

Read on for an example.

Comments closed

Refreshing Selected Tables in Power BI Desktop

Published 2020-04-22 by Kevin Feasel

Gilbert Quevauvilliers answers one of the great mysteries in life:

When working with Power BI datasets I often want to refresh multiple tables, BUT not all the tables in my model. (This often happens when I made a change to underlying data sources and I want to make sure it is working as expected in Power BI Desktop)

I recently found that I could do it by doing the following steps below.

When you have one or two big tables and you don’t make any changes to those tables, it can be a pain waiting for them to refresh. This is a great alternative.

Comments closed

Calculation Groups in Analysis Services and Power BI

Published 2020-04-21 by Kevin Feasel

Kasper de Jonge walks us through calculation groups:

In most cases the business wants to see some common calculations done over these measures like YTD, QTD, YoY% etc. That means you must make a new measure for each of the calculation over the base measure. This leads to measure explosion in your model as you need to add a new measure for each variation. Very quickly this leads to not four measures but sixteen and more creating a management headache.

In come calculation groups. With calculation groups you can create a group that can apply common calculation over base measures. It’s hard to explain so let’s just dive in on how that works.

Click through for a demo of how to enable and use calculation groups.

Comments closed

Building FAQs on a Power BI Dashboard

Published 2020-04-21 by Kevin Feasel

Evan Rhodes takes us through building out a FAQ for a dashboard:

I suppose you could add a bunch of text boxes with questions and answers. But, what if you have several questions and there isn’t enough space? I’m reminded of something a fantastic boss once told me, “Never pass up an opportunity to wow someone and grab their attention with your work.” So, let’s add some wow effect to this by leveraging the bookmarks and buttons functionality.

Bookmarks and buttons allow us to create a user experience that is intuitive to the user and that allows them to navigate around the page easily by just clicking. In this case, click on a FAQ and the answer appears. Click on the FAQ again or a different FAQ… I think you get the point.

If you need this on the dashboard itself, this is probably the right way to do it—there for the one time you need it and hidden away the rest of the time.

Comments closed

Mistakes to Avoid in a BI Platform Migration

Published 2020-04-20 by Kevin Feasel

Chris Webb covers five things to consider when migrating your BI platform, using Power BI as an example:

Every report has a data source and getting source data in the right format for your BI platform is a substantial task – so much so, that you might be tempted to put Power BI on top of the data sources you have created for your previous BI platform with no changes. However different BI platforms need their data in different formats. Many BI platforms like their data munged together in one big table, sometimes even with data at different granularities in the same table. Power BI, on the other hand, likes its source data modelled as a star schema (you can find out what a star schema is and why it’s important here). If you don’t model your data as a star schema you may find that you see incorrect values in your reports, that report performance is poor, and that it’s a lot harder to write the DAX calculations that you need.

Four out of the five fit just as well with any other data platform technology.

Comments closed