hierarchy for measure

[pey]

Hi,

I would like to seek some help with building hierarchy - I realize I am not able to create hierarchy with measures, only able to do it with non-measure parameters. Please see the dataset below.

I would like to create a hierarchy called Total exports: Exports of goods and services (higest level), consisting of "Export of goods" and "Export of services" so that I can put it into a table and click + to expand to lower level..

Thank you.

[TomMartens]

Hey @pey ,

unfortunately this is not possible.

A very simplyfied explanation for this is: This is because of the mechanics of the underlying data store that fuels Power BI, it's the Analysis Services Tabular engine. This engine considers facts  being "simple" numeric columns that can not be arranged in any hierarchical form.

To overcome this "limitation", you have to think a little outside of the box. You have to pivot your numeric columns "Export of goods" and "Export of services" in to a single column, call this column something like "Level 0" and the corresponding values into a Value column. Create a second (being precisea a 3rd) column and call this column "Level 1" This Column contains a single value: Exports of goods and services.

Then you can create something that you can expand/collapse.

But from the screenshot you provided this is a much more complex design adventure, creating a structure that contains all "measures" and also forms allows to create a simple structure.

Maybe you can also consider using visuals that allow creating "sums" like the Zebra BI visual: https://zebrabi.com/power-bi-custom-visuals/tables/

Hopefully, this provides some ideas on how to tackle your challenge.

Regards,

Tom