steve – Microsoft Power BI

Power BI – Use Decomposition Tree

août 4, 2023août 4, 2023steve

A decomposition tree is a powerful visualization tool in Power BI that allows users to break down a measure (numeric value) into its contributing factors in a hierarchical manner. It’s particularly useful for understanding the key drivers behind a specific metric and gaining insights into the data at different levels of granularity.

Example

To create a Decomposition Tree visual in Power BI, you can use the « Decoposition Tree » visualization type. Once you drag your measure into the field well, the visual updates to showcase the aggregated measure.

In this simple example we are going to consume below Data:

Please find below some example of implementation:

Scenarios

Here are some scenarios where using a decomposition tree in Power BI can be beneficial:

Root Cause Analysis: When you want to identify the root causes of a particular metric’s performance, a decomposition tree can help you visualize the different factors influencing that metric. For example, understanding the key drivers behind a decrease in sales or an increase in customer churn.
Exploratory Data Analysis: If you have a dataset with many dimensions and measures, a decomposition tree can assist you in exploring and understanding the data structure more easily. It can reveal patterns and correlations in the data that might not be immediately apparent in traditional visualizations.
Performance Evaluation: In situations where you want to assess the performance of different groups, products, or regions, the decomposition tree can break down the overall metric into subcategories, allowing for quick comparisons and identifying areas of strength and weakness.
Forecasting and Budgeting: When preparing budgets or forecasts, the decomposition tree can help in understanding historical trends and contributions from different factors, aiding in making more informed projections.
Customer Segmentation: For marketing or sales analysis, the decomposition tree can be used to segment customers based on their attributes and analyze how different factors impact their behavior or spending patterns.
Process Optimization: In business process analysis, the decomposition tree can be used to identify bottlenecks or inefficiencies by breaking down metrics related to the process steps.
Supply Chain Analysis: For supply chain management, the decomposition tree can help in understanding the cost breakdown or inventory flow through various stages.

Overall, the decomposition tree is an excellent option when you want to dive deeper into the data to understand the underlying drivers of a specific metric. It allows you to interactively explore and analyze data at multiple levels, making it easier to grasp complex relationships and gain valuable insights. However, be cautious about using it with very large datasets, as the visualization can become overwhelming and less effective.

Find more information in Microsoft Learn
https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-decomposition-tree

Power BI – Use ‘Waterfall’ visual

août 4, 2023août 4, 2023steve

In Power BI, the « Waterfall » visual is used to display changes in a quantitative value over a sequence of categories. It is particularly useful for showing how an initial value is affected by various positive and negative factors, leading to a final total. The Waterfall chart is similar to a stacked bar chart, but it includes connectors that represent the cumulative effect of the individual values.

Example

To create a Waterfall visual in Power BI, you can use the « Waterfall Chart » visualization type and assign the appropriate fields to the « Category » and « Value » buckets in the chart properties.

In this simple example we are going to consume below Data:

Prior to add the visual, please import the data as illustrated below:

Now, you can add the « Waterfall » visual and drag the Bucket and sales values:

Scenarios

Here are some scenarios where using a Waterfall visual in Power BI can be beneficial:

Financial Analysis: The Waterfall chart is commonly used in financial reporting to show the changes in revenue, expenses, or net income over time or across different categories.
Sales Analysis: It is useful for analyzing the components of sales performance, such as the impact of different products, regions, or customer segments on the overall sales.
Inventory Analysis: To show how the inventory levels change over time or due to different factors like production, sales, and replenishment.
Budget Analysis: When comparing the actual budget with the projected budget, the Waterfall chart can visually demonstrate the variances.
Profit and Loss Analysis: To illustrate the profit and loss contributions of different business segments or departments.
Project Progress: It can be used to visualize the progress of a project over time, breaking down completed tasks and remaining work.
Cohort Analysis: For showing how different cohorts evolve and contribute to the overall performance.

Remember, the Waterfall chart is most effective when you want to emphasize the cumulative effect of each component and how they lead to the final value. However, it might not be suitable for displaying large datasets with many categories, as the chart can become cluttered and less informative in such cases. Always ensure the chart is easily interpretable and meaningful to your audience.

Find more information in Microsoft Learn
Waterfall charts in Power BI – Power BI | Microsoft Learn

Power BI – Get detail from Web API

avril 18, 2023août 4, 2023steve

The purpose of this article is to describe how to retrieve extended data in the Microsoft Power Query Editor. The main idea is to return, for each rows, information from a Web API.

Start with the Microsoft Power BI application, described in previous article in order to already have an application with a list of Crypto Currencies lodad. In this article we will retrieve, for each currencies, the corresponding prices through Web API provided by https://www.coingecko.com/en/api/documentation
https://powerbi.funspirit.ch/microsoft-power-bi-get-data-from-web-api/

Provided by https://pixabay.com/images/id-4481815/

Get Web API Link

In this example we will import all Crypto Currencies from https://www.coingecko.com/

The goal is, for each crypto currencies, to call a Web API to retrieve the price in CHF. Somehow, similar call will be performed

https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=chf

Each call will return similar value

{"bitcoin":{"chf":29499}}

For each crypto currency, the price will be loaded.
At the end of this article, you will have a Microsoft Power Bi Application, with a list of crypto currency with corresponding prices.

Get Data

In order to connect to Web API, Launch the Power Query Editor, select in « Get Data« , the « Web » option and click on the button « connect«

Select the « Advanced » option and paste the URL https://api.coingecko.com/api/v3/simple/price in the first URL parts:

Add multiple parts, as illustrated below and click on the button « Ok« :

Right click on the query and click on « Advanced Editor« . Below formula had been created:

let
    Source = Json.Document(Web.Contents("https://api.coingecko.com/api/v3/simple/price" & "?ids=" & "bitcoin" & "&vs_currencies=" & "chf")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded bitcoin" = Table.ExpandRecordColumn(#"Converted to Table", "bitcoin", {"chf"}, {"bitcoin.chf"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded bitcoin",{{"bitcoin.chf", Int64.Type}})
in
    #"Changed Type"

Embed the code into a function, called GetPrice, as illustrated below:

Update the code to get dynamic parameters instead of hardcoded values:

Click on the button « Done«

Test Web API

Enter corresponding test values for cryptoId and currencyId, like bitcoin and chf and click on the « Invoke » button:

Web API call will be performed and value will be shown

Rename your query, like « GetPrice » to easily integrate it:

Congratulation, we have now a function GetPrice which could be called in any queries.

Integrate Web API call for each items

This function could be used in any loader.

Power BI – Get Data from Web API

mai 9, 2022août 4, 2023steve

The purpose of this article is to describe how to get data from a Web API. In this tutorial, we will connect to a WEB API and retrieve a list of Crypo Currencies.

https://www.coingecko.com/ is providing, for free, a list of Web API which could be used to retrieve information related to crypto currencies.

Get Web API Link

In this example we will import all Crypto Currencies from https://www.coingecko.com/

Coingecko Documentation is availalbe in https://www.coingecko.com/en/api/documentation.
From this list, the Web Api « /coins/list » will be selected:

Get Data

In order to connect to this Web API:

Launch Power BI Desktop
Click on « Get Data«
Select « Other« , « Web«
Enter the URL parts https://api.coingecko.com/api/v3/coins/list?include_platform=false and click on the « Ok » Button
Once Imported, below values will be imported (ID, Symbol and Name)
Perform any required transformation and, once finished, click on the « Close & Apply » to get those fields available in Microsoft Power BI