SAP-formatting in Power BI: this is how it works

SAP reports go hand in hand with hierarchies, currencies, keys and descriptions. We all know that. But with Power BI it works slightly differently, as these functionalities are not displayed by default within the tooI itself. In this blog, we will explain how you can add the functionalities into a clear layout in Power BI as well.

Don’t like to read? Then watch this episode of McCoy TV where Wessel takes you through the steps .

1. Display Hierarchies

In SAP, we have one object where we can dynamically select and navigate a hierarchy.

With Power BI, you need to take a few extra steps. If you want to display hierarchies effectively in Power BI, start by selecting the hierarchy in the query designer and load it into the report. Be sure to select only one hierarchy per dimension. After it is loaded, Power BI will automatically create a level-based hierarchy, displaying each level in the columns.

Now you can create a hierarchy object in the report and easily navigate through the hierarchy. Useful for exploring data at different levels of granularity.

2. Restricted measures in hierarchies

By using restricted measures, you receive accurate insights regardless of the level of detail chosen.

In SAP, it is easy to create restricted measures by filtering specific levels of the hierarchy. In fact, you do so based on a single hierarchy object.

In Power BI, it works as follows: Use the DAX measures for each specific level you need. Not sure what level a value is at? Then use the OR function to search across all levels.

3. IDs and descriptions

The SAP function Info Objects allows you to display the ID, description, or both in the same column. You can also edit this on the spot while building and using the report.

In Power BI, you can use the properties of each dimension to select the non-compounded key. This selection gives you access to an ID column and a description column for each dimension.

In Power BI, you can use the properties of each dimension to select the non-compounded key. This selection gives you access to an ID column and a description column for each dimension.

4. Currencies and units

Finally, I want to talk about formatting currencies and units. You can link these to key figures in SAP. They are then automatically displayed in the output of the key figure.

By selecting the "unit of measure" property in the query designer, we can create DAX measures that are automatically formatted based on the selected currency.

You can then reuse this formatted measure in other calculations. This functionality saves time and ensures that reports comply with the correct currency and unit of measure standards.

Need help?

Do you have any questions? Or do you need help with your SAP or Power BI projects? At McCoy we're glad to help you!