Hierarchy into a hierarchy. Is it possible?

[nieps]

Hi,

I'm trying to build a Profit and Loss report. Information is groupped/organised in different levels and these may have x or y sub-levels.

at the end, the 4digit accont number must be shown.

this means tha in some cases, the account must be shown at a 3rd (x)  info level, other times at 4th (y) info level.

this makes it really difficult to me to crear a master table.

I thought that using hierarchies (putting one into another) would help, but I can't see a way to do it.

is it possible? if so.. how?

thanks a lot in advance

[harshnathani]

Hi @nieps ,

You can have a look at the below videos to create and manage hierarchy

https://www.youtube.com/watch?v=oEOaqyaZTZw

https://www.youtube.com/watch?v=MK1jwHz8hoc

Regards,

HN

[amitchandak]

@nieps , Not very clear. Can you share sample data and sample output in table format?

But refer if this can help

https://community.powerbi.com/t5/Desktop/Traditional-Financial-Statements/td-p/7223

https://grantthornton.eu/2019/08/07/profit-and-loss-statement-power-bi/

https://www.thebiccountant.com/2017/01/16/easy-profit-loss-and-other-account-statements-part2/

[parry2k]

@nieps you cannot merge two hierarchies but you can add more column to your existing one. not sure if this answered your question.

[nieps]

thanks for your answer, @parry2k 

I've already built my report the way you propose, but I don't really like the result (which I call "the quick and dirty solution").

@amitchandak  I'll have a look at the links you shared.

in the meanwhile, I'll try to show you where my problem lays

Solution 1: the Q&D one which I'm already using

using this data structure as 'master data', I can create a hierarchy putting all levels and account column under Level 0

but, when creating the report (list), and you do the drill down, you'll find that in some cases you've got to drill 'empty' levels down until you get the account (eg. all lines in the table with empty fields)

Solution 2: 

whit this data structure I can also create a hierarchy in powerBI, and the report looks perfect! but it's really hard to handle and mantain in a regular basis, even more if I whant this table to be updated with an automatism.

any other idea or suggestion?

thank you guys!

[v-diye-msft · Accepted solution]

Hi @nieps 

Do you mean you'd like to set your original data transformed as the solution 2? Move the right column value to fill the left empty one?

You might try the power query something like this:

= Table.ReplaceValue(#"Changed Type","",each [B],Replacer.ReplaceValue,{"A"})

[nieps]

hi @v-diye-msft ,

your proposal looks good.

I wanna try it, but I'm not sure where to use the formula. you mean when I load the data and "transform" it via power query?

by the way, would this solution work even if I need to apply this formula to several columns?

I mean, as it is shown in the pics of my previous post, there are several levels; in fact, in my pics, there is one more level missing. (hu hu!)

[v-diye-msft]

Hi @nieps 

I wanna try it, but I'm not sure where to use the formula. you mean when I load the data and "transform" it via power query?
Yes.

by the way, would this solution work even if I need to apply this formula to several columns?

It works.