Erfolgreiches Reporting mit Business Intelligence: MS Power BI Tipps

Erfolgreiches Reporting mit Business Intelligence! Das sind die 8 Power BI Tipps von unserem Experten.

1. Umdenken gefragt: Veränderung beginnt im Kopf

MS Power BI ist die Business Suite Ihrer Wahl für Ihr zukünftiges Reporting? Dann erfüllen Sie bereits die besten Voraussetzungen für sicheres Controlling. Häufig verfällt man nach einer Umstellung auf ein neues System oder Tool jedoch in alte Muster zurück. Das heißt: Wer mit Business Intelligence arbeitet, sollte sie auch nutzen: Adieu, Excel, hallo interaktives Reporting!

Die Zeiten, in denen Sie Tabellen nach Excel exportieren, Screenshots machen, Excel-Auswertungen mit monatlichen Geschäftszahlen per Email versenden und anschließend in Power Point präsentieren sind vorbei: Bauen Sie das Reporting stattdessen direkt so auf, dass es sämtliche Anforderungen erfüllt und bestenfalls jederzeit live für Präsentationen abgerufen werden kann.

2. Gut Ding braucht Weile

Die Arbeit mit Power BI ist nicht zwingend intuitiv und benötigt eine gewissen Einarbeitungszeit. Die regelmäßige Weiterbildung im Team ist daher relevant. Wie immer trifft auch hier zu: Wer ein Thema mehrere Monate liegen lässt, erschwert sich den Einstieg unnötig.

3. Updates im Blick

Informieren Sie sich auch über neue Funktionen durch Updates, die Ihre Arbeit erleichtern und noch effizienter machen können. Infos zu Updates und neuen Features finden Sie auch in unserem Blog!

Regelmäßig Updates auszuführen ist immer sinnvoll.

4. Besseres Reporting durch gepflegtes Quellsystem

Es ist ein Grundsatz, der auch für die Arbeit mit Power BI gilt: Lieber gleich sauber arbeiten statt im Nachgang alles ausbessern zu müssen: Wer also früh damit anfängt, die Daten in hoher Qualität zu erfassen, hat schlussendlich im Reporting weniger Unstimmigkeiten und Ausbesserungsbedarf. Zudem sollten immer darauf achten, dass Ihr BI-Tool nicht künstlich Daten erzeugt, die in den Quellen nicht existent sind.

5. Nicht verwendete Tabellen nicht in das Datenmodell laden

Die Versuchung ist oft groß: Sie haben es geschafft, die richtigen Quelldaten zu finden und diese in Power BI einzubinden – und laden in Folge zusätzlich die ein oder andere Tabelle in Ihr Datenmodell, obwohl Sie diese in nächster Zeit kaum verwenden werden. Im ersten Moment scheint der Gedankengang „Was wir haben, haben wir“ vielleicht korrekt.

Aber: Das Datenmodell zu überfrachten ist nicht ratsam. Laden Sie deshalb nur die Tabellen in Power BI, die Sie auch tatsächlich für kommende Auswertungen benutzen möchten. Das macht die Datenmodellierung weitaus übersichtlicher, Sie verbauen sich keine Wege, Ihre Daten laden schneller und Sie verschwenden keine unnötige Zeit. So lässt sich optimiert arbeiten! Ihre Vorgehensweise sollte dem Prinzip „So viel wie nötig, so wenig wie möglich“ folgen, um effizient zu bleiben.

Viele Tabellen sind keine Arbeitserleichterung, sondern eher unübersichtlich.

6. Browsererweiterung zur exakten Farbbestimmung installieren und verwenden

Ihr Unternehmen hat ein schönes Corporate Design und Sie möchten dessen Farbgebung auch in Power BI wiedersehen? Am besten benutzen Sie dafür ein Pipetten-Tool (Farbwähler oder Color Picker), um die exakte Farbe zu bestimmen. Installieren Sie im ersten Schritt eine kostenlose Color Picker–Browsererweiterung (z.B. Eye Dropper für Chrome).

Gehen Sie anschließend auf Ihre Homepage, wählen Sie die entsprechenden Farben mit dem Curser aus und bestimmen Sie mithilfe der neuen Erweiterung die jeweiligen Farbwerte, die Sie in Power BI verwenden möchten. Sie bekommen zu jeder Farbe Hexadezimal- und RGB-Werte angezeigt.

Mit einem Pipetten-Tool bestimmen Sie bequem die relevanten Farben für Ihr Farbschema in Power BI.

7. Json-Datei für Farben erstellen

Für die komfortable Anwendung Ihres Corporate Designs empfiehlt es sich zudem, eine eigene kleine json-Farbdatei zu erstellen. Im ersten Schritt laden Sie sich einen freien Texteditor (z.B. Notepad++) herunter und kopieren das kurze Skript in eine leere Seite hinein. Die acht Zeilen unter dem Wort dataColors beinhalten in den Hochklammern „“ Hexadezimalwerte, die sie mit der installierten Browser-Erweiterung generieren können.

{

„name“: „Ihr Firmenname“,

„dataColors“: [

„#0063B1“,

„#03BEEE“,

„#000000“,

„#A2AAAD“,

„#ffffff“,

„#ffffff“,

„#ffffff“,

„#ffffff“

]

}

Eine Farbbibliothek sorgt für ein einheitliches Farbschema in Ihren Visualisierungen.

So sieht die Benutzeroberfläche einer Notepad-Seite aus.

Im Texteditor werden die Farbwerte für das individuelle Farbschema eingetragen.

Im Anschluss speichern sie diese Datei als .json-Datei ab. Im letzten Schritt importieren Sie die entsprechende Datei in Power BI unter der Option Anzeigen (englisch: View) -> Nach Designs suchen (englisch: Browse for themes). Jetzt finden Sie in jeder Ihrer Power BI-Visualisierungen ausschließlich Ihr eigenes einheitliches Farbschema wieder!

Die json-Datei importieren Sie im letzten Schritt mit Power BI.

8. Die richtige Menge an Visualisierungen im Bericht verwenden

Sie wollen ansprechende Reports mit hoher Aussagekraft erstellen? Guter Ansatz, nur: Woher wissen Sie, wie genau ein ansprechender Bericht aussehen muss? Die Internetrecherche liefert hier schnell zahlreiche Treffer für vermeintlich gute Designvorschläge, bei der jedoch sorgsam selektiert werden sollte:

Überfrachtete und unstrukturierte Berichtsseiten mit zu vielen Visualisierungen sind genauso wenig zu empfehlen wie Berichtsseiten mit zu wenig Visualisierungen oder unpassenden. Redundanzen und der falsche Einsatz von Farben erschweren zudem die Datenanalyse.

Wir empfehlen für ein strukturiertes Berichtsdesign: Zeigen Sie beispielsweise im oberen Bereich relevante Daten und KPIs an, bilden Sie im mittleren Bereich Filter und Schaltknöpfe ab und schließen Sie mit Diagrammen und Karten.

Power BI: Tips, Tricks, and More — Kate from Oz

Beginning Power BI Info

Best practice is to build your reports in PBI desktop, then publish to PBI Service (online). And then, when you update your report in Desktop, you can just publish it to the Service (online) again (it will ask you if you want to replace what you already have there – say yes). From PBI Service, you can build dashboards, create workspaces, share with others, etc.

Microsoft Power BI Guided Learning - Great resource but make sure you watch the videos with it too!

RADACAD.COM This is a really great resource. This is just the “beginner PBI” info but I scroll through his posts all the time to get help with issues or find ideas/inspiration when designing PBI reports and dashboards.

Guy in a Cube is another great resource for learning to use Power BI, and more advanced skills.

Another good beginner reference – 10 Common Mistakes in PBI and how to Avoid them.

How to Speak Power BI - learning the software lingo to help avoid miscommunication/misunderstanding. Beginning: and intermediate:

Always create/import a “master” Calendar table into your PBIX, especially if your database doesn’t have a DimDate table. Blog post coming ASAP with options.

Quick Guide video for PBI dashboards

Microsoft Power BI Blog is the best place to see what new features are added to PBI on a regular basis.

The PBI community forums are a god-send. Here is the desktop main page, but you can find the PBI online info from here.

and Power BI User Group!

Remember that you will primarily use DAX and PowerPivot(M) as your coding languages in PBI. You can also use R, but I’m not as comfortable or progressed in learning it as I am with DAX/M.

Advanced Power BI Info

Using Bookmarks, Selection pane, Buttons, and personally created “toggles" to create interesting yet easy ways to visualize and organize your data. Will get an embedding PBI report as an example soon. But this is a good blog post on setting up ways that Amanda from Microsoft has shown to incorporate buttons/bookmarks into your design.

Creating a “formulas” table for all your PBIX measures is my preferred method of setting up my PBIX. Once you hide the blank column, save and close the report, the Formulas table will be at the top of your Fields pane. Blog post with video coming ASAP.

Filtering on a card – I use this a lot. Go down to the Eno1978 response about starting with a table first, then switching to a card. (Aug 2018: Sadly, this trick doesn't work anymore.)

If/Then/Else statements and conditional columns in PBI. This is super useful and PBI makes it fairly easy to set up conditional columns and T/F columns within PBI Desktop for your imported data.

HEX tile Maps for Power BI – something really cool that I haven’t started diving into yet but will soon.

MS Project Online into PBI – This is great basic info. Here is what I do: 1) I use the specific MS Project Online content Pack for PBI to start, 2) set up loading my data, 3) Go into the Query Editor and then look at what has already set up to be pulled into PBI from Project Online, 4) click on a table on the left menu, you can look at the right side bar – Applied Steps, and double click on [Removed Other Columns] and you can add in more columns from the table that weren’t already set up automatically, 5) Under the ODataUrl table on the left, you can look to see all the potential data tables available to you and then add those to the rest, 6) super important to look at the relationships between your data tables.

- MS Dynamics CRM into PBI is very similar to pulling Project Online data, here’s some basic info:

Common query tasks in PBI, also discusses a bit about using the Advanced editor.

Text After/Before Delimiter, extract text, etc. : Go to [Query Editor], then the [Add Column] tab, and then beside (Format) and below (Merge Columns) you’ll see [Extract] – this is super useful!

Setting up DateDiff to calculate number of days between start date and finish date, and if you want to know DateDiff from TODAY (current date), replace Today() for the [EndDateField] in the accepted solutions on this post:

Data alerts in PBI – can set up on PBI Service dashboards. Can use Microsoft Flow to set up to email people when these alerts are triggered.

Custom PBI visuals – I’m giving you both the new site and GitHub page – lots of great info on both. Lots of awesome custom visuals. You can also access custom visuals from within Power BI Desktop. First one you should download, import into PBI, and learn to use is the Infographic Designer. FYI: You’ll have to import the custom visuals you want to use for every new PBI desktop report you create.

New -

GitHub -

Have multiple attribute columns that are the same list of names, descriptions, or titles but want to be able to apply ONE slicer to all of them in your Power BI report? This will help you figure it out -

Dynamically changing shapes in PBI. This touches on building a table with image urls to relate your data to a “state” and have it show as say a red, yellow, or green circle in a PBI table.

Trailing Twelve Months calculations for Power BI.

Along with TTM link above, what if you want to allow people to dynamically choose how many months (or years, quarters, days, whatever) on your Power BI report page? Using N with this table and measures will help you get this figured out. Thinking it could also provide a way to all people to visualize say Top N Clients with a dedicated slicer on the page.

RADACAD Power BI Helper tool will amazing but I’m still learning how to use it. Download it here:

More on using color graphics in a table/matrix to show data – This is for KPI indicators:

Big question I get often: What is the difference between a Measure and Calculated Column? Here is a basic explanation: Calculated columns are very similar to regular columns that we find in most datasets. The difference is that calculated columns are the result of computations by using two or more columns or using columns from different tables created by us within PBI. They can be used when we want to perform row-wise calculations. Measure, on the other hand, is similar to a calculated column but does NOT occupy any physical memory and the results can’t be seen in the form of a column. We typically use a measure when we want to perform dynamic computations on a group of rows or by grouping data together.

TIPS AND TRICKS!!

Make sure you turn on many of the “Preview” features in Power BI Desktop (and check periodically for new items added here). Go to File Options and Settings Options Preview Features under “Global.” Also remember that you have a lot of options and settings you can set/change once you get to the “Global” and “Report” menus under the Options in PBI Desktop.

In PBI Desktop, go to the [View] tab, and you can manually set how you would like the page to display on mobile devices – then publish to PBI Service, and then you can view mobile version.

Use ctrl c and ctrl v to copy and paste a visualization both on same page and other pages

Use ctrl a to copy and then paste all elements on a page

Unless you manipulate the formatting of your columns in the advanced editor, I tend to not set my columns as whole numbers, currency, percentage, date, etc. in [Query Editor] but wait until I’m in the main area and select the “data table” icon on the left menu, then select the [Modeling] Tab at the top, and then change things.

If you are using percentages in your data tables, and especially if you add a custom column that is a percentage, don’t multiple your formula by 100 to give you a percentage in your data table, leave it as a decimal number. THEN under the left “data table” tab, go to the [Modeling] tab, and then set your column as a percentage – this will then multiply your decimal by 100 and include a % symbol with your data on your report pages.

Merge/Append Tables is a great feature, just spend a little time googling the various differences.

Managing Relationships between your data tables is very important. This needs to be properly set so that you can combine data columns from related tables into one chart/graph/table.

Format painter: click a visual, click format painter, then click on a same type of visualization to switch to former visual’s format

If you want to filter out data, say a column that is true or false and you want to display the count of true items, visualize as a table, filter the visual, then with it selected, click on the card visualization

In the Query Editor, click on the Advanced Editor – I use this a lot for manipulating my data, including getting a table to behave a specific way and being able to copy most of the info in the Advanced Editor to another similar data (takes out a ton of steps if you edit that table in similar way as a previous table).

Remember that much of your data values for PBI report visuals will be a sum/average/median/don’t summarize – you can change these to reflect how you want your data to behave.

Color and Design

Color Codes (PBI uses Hex#) -

Help with determining complementary colors, etc. -

Wanting to figure out a specific color from an image, etc. -

Advanced Color Theme Generator for PBI reports -

Create your own clip art to overlay on tables/charts in PBI without having photoshop or the like:

Use Illustrator or the free Inkscape to create high quality PNG page backgrounds and wallpapers. SVG files are possible, but best practice is to use PNG image files. Example files: SVG and PNG

The Infographic Designer is also the first custom visual you should import and learn to use!

Tips for Designing Great Power BI Dashboards and Reports

Are you looking for information regarding Microsoft Power BI? Would like to learn the best practices for designing great Power BI dashboards and reports? Look now further, we have got you covered!

In this blog we provide you the summary of the top tips. We also link you with some of the best experts in Power BI. You will find the links to the original articles for further information regarding designing great Power BI Dashboards.

Editor’s Note:

Before we jump in to the articles we’d like to note something.

In a case you are optimising a dashboard or a report to be used solely on Digital Signage displays keep these two rules of thumb in mind:

Focus on the most important information

Have less content

Use larger fonts

The audience Digital Signage displays are typically looking at the displays from further distances compared to the laptop.

Make the content stand out and easy to view. Using larger fonts also forces to minimize the amount of content – circling back to rule number one

Without further ado, let’s dive right in and take a closer look at the tips and articles that help you to design great dashboards and reports.

Guy in a Cube

Make your Power BI report POP with a custom background

Often times the difference between someone using your report and not consuming your report is the way it looks. In this video Patrick gives you a simple tip to use a background image that makes the report POP and nice, something that people want to look at. Take a look at this video. This might just do the trick

Guy in a Cube is a popular YouTube channel that helps you master Business Analytics on Microsoft Business Analytics stack.

Microsoft Docs

Tips for Designing Great Power BI Dashboard

This article, by several contributors, will help you make the most important information stand out, make the dashboard clean and uncluttered.

The summary of the top tips in this article are:

Consider your AudienceTell a Story on One screenMake use of full screen modeAccent the most important informationPlace the most important informationUse the right visualizations for the right data

They also have further links to a white paper and a video in the article.

The entire article can be found here: Tips for Designing a Great Power BI Dashboard

sqlbi

The 15 rules to design a perfect dashboard

Marco Russo gives you Power BI design best practices in this webinar recording.

The summary of the design best practices are:

Design for a targetKeep everything at a glanceKeep it simpleAlign elementsBe consistentHighlight the most relevant informationBe clearStart from zeroShorten the numbersShow the contextChoose the right colorsDesign dashboards, not reportsShow variationsLeave the noise offPick the right parts

You can find the webinar in its original location here: The 15 rules to design a perfect dashboard

Predica

5 Power BI Tips To Make Your Reports More Appealing And User-friendly

In the original article Paweł Szczecki gives you 5 tips on how to make the most of your Power BI Reports.

The summary of the tips:

Simplicity – don’t go too fancy with VisualizationsContext – Interrelations between elementsDivide and conquer (or slicing and dicing) – filtersHigh or Low perspective – hierarchiesMeaningfulness – think about the message rather than the graphics

The entire article can be found here: 5 Power BI Tips to Make Your Reports More Appealing and User-friendly

PowerPivotPro

My Top 5 Power BI Visual Design Practices: Transforming Good to Great

This blog post by Reid Havens offers you 5 concrete tips that will help to make sure your Power BI Report looks good, tells a story, pops, or his personal favourite “is aesthetically pleasing”

The summary of the tips in the article:

Company logoLines to help divide sections of the reportData timestamp to show how recent the refresh wasReport formattingDedicated DAX measures table

The entire article can be found here: My Top 5 Power BI Visual Design Practices: Transforming Good to Great

Further Reading you might find interesting