Removing first rows (totals) if repeated, keep if unique - hierarchy

[foyiq]

I have a table with many layers/levels characterizing an item. For example, Assets-CurrentAssets-TradeReceivables-ShortTermTrRec-ThirdParties. Some of the items do not have 4-th and 5-th levels. But all the levels have their totals as the first row (in each level). Now I want to get a flat table without entries of totals.

What I need to do is to leave the unique three-level rows (i.e. those that do not have further specifications like ThirdParty or RelatedParty), and remove the first row of the four and five-level rows to get rid of totals.

What I managed to do so far is see (via grouping) which rows have duplicates in, say, third level. Now I need to keep the rows, where the number of duplicates is 3 (as i have taken three periods), and remove the first row in groups where the number of duplicates is higher than 3. I would also appreciate if you could help me link the threshold number of duplicates to the number of unique files, whith their names available on a separate column.

At the moment I am doing this all in Query editor mode, so as to get to a certain structure later.

I would further use any suggestion to repeat the steps with regard to 4-th level, too.

I'm relatively new to PBI and would appreciate if recommendations would involve Query Editor steps

Many thanks for your time.

[v-huizhn-msft]

Hi @foyiq,

It’s difficult to reproduce your scenario based on your description. Is there any chance to post your sample data or screenshot for analysis? So that we can post solution which is close to your requirement. Thanks for understanding.

Best Regards,
Angelia

[foyiq]

@v-huizhn-msft
Hi, Angelia, thanks for reply!

I am sorry for the delay, as the urgency of the task made me work the issue the other way (by creating category table and extracting only needed values matching certain balance items). If you allow, I would like to change the question, as the next stage appears to be problematic for me, as I am relatively new to DAX. So, to clarify, initially I had a table of the following format:

There are some measures, which I need to extract from this table; it contains a number of tables from files in the source folder. The end target is working capital and its main items (accounts receivable, inventories, accounts payable). I got the WC and its components initially by reshaping the data in power query and unpivoting it, so as to make it flat for analysis, and then by creating measures which include the balance items needed in calculation depending on the category table.

The tricky thing is that these figures are static, while i need the dynamics as well. To find the difference within a single file (same period and same planning type - actual, budgeted, and current plan), i just duplicated the query and edited it before unpivoting, so as to subtract beginning balance column from the ending balance column.

and so on.

Now the hardest part for me is to be able to extract the changes across different periods (diff. quarters), planning types (plan, budget, and actual figures), and different balances (ending and starting). To clarify, among main tracked changes might be the previous actual ending balance vs current plan starting balance (static-static=dynamics); or comparison of dynamics within plan and within actual results --dyn(ending plan-starting plan) vs dyn(actual ending - actual starting)--.
I have no idea, how to move forward, to be honest, and would appreciate any instructions or even hints.

Many thanks in advance.

Best regards,

Foyiq
--------
I think I might be able to post a sample of my query and dax actions, if you conclude that the sample screenshots are not enough. It just might take time for me to mask the sensitive figures.

[v-huizhn-msft]

Hi @foyiq,

I receive the email you have resolved the issue? Congratulations, welcome to share your workaround and mark it as answer.

Thanks,
Angelia

[foyiq]

Hi, @v-huizhn-msft,

I just had to change the approach a bit to get to what was needed. Now I just wanted to close the issue, as it was no longer actual, so as not to keep it here.

Yours,

Foyiq

[v-huizhn-msft]

Hi @foyiq,

Thanks you for your detailed information, I suggest you open a new thread if you have new question. There will be others take and the case regularly. Thanks for understanding.

Best Regards,
Angelia