Dynamically change item's position in an hierarchy

[michaelshparber]

Hello,

I have a Balance Sheet hierarchy that aggregates accounts into 3 levels.

I need to make one exception to this hierarchy:

If at any specific month there are "Accounts Payable" (Lev2 in hierarchy) accounts with positive balance ("Balance Amt" measure) then I want these accounts to be shown in a different place in the hierarchy, in "Advances to suppliers", under Assets.

I have searched a lot and played a lot and came with this measure : CALCULATE(sumx(Accounts, if([Balance Amt]>0,[Balance Amt],blank())),FILTER(ALL(Accounts),RELATED('Balance Sheet Hierarchy'[Lev2])="Accounts Payable"))

but it is not quite working.

My 2 main requirements are:

1. I need to keep the relationship between Accounts and Balance Sheet hierarchy table. I DID manage to solve this problem for disconnected table, but I need the relationship for other purposes

2. I need all the upper levels to correctly aggregate all of the accounts including the exception.

Please help

Attached is the simplified model:

https://1drv.ms/u/s!AoP_9ampPIT781YQxDp3mrEoS9L0?e=EZw4lC

Thank you
(@ImkeF I have searched you posts, but didn't find anything maybe you have some suggestions, thanks a lot!)

[TomMartens]

Hey @michaelshparber ,

you have to be aware that a measure "just" creates a scalar value, no matter how complex the expression might be. To change the grandparent (the member in level 2) and the parent (the member in level 3) of an Account you have to use calculated columns, but using calculated columns is static and the expression(s) will be only evaluated once (basically), during data refresh.

This simple truth already has been mentioned by @daxer-almighty 

From my personal experience, there are two approaches

My favorite approach is the one described by @ImkeF, mainly based on data modeling.

I also use "duplicate" parents.

Then there is some logic during the data processing (I often imagine the table iterator functions like SUMX(...) as data processing tasks), this logic allocates aggregated transaction values to a parent. This allocation is controlled by flags, like "hide if negative", "show if negative". I use "show if negative" for all the "normal" accounts as well.

The second approach relies on "traditional" data processing (M or whatever you are using), here I create the "Account to use" during data refresh or (even better)  in the data warehouse. The "Account to use" is derived from the "Account" by a simple logic: IF the SUM(ACCOUNT) < 0 then ACCOUNT_n else ACCOUNT_p GROPUP by (ACCOUNT  PERIOD).

Sure,  there will be two accounts once again, but now it's straight forward, as a one-to-many relationship with filter direction Single can be used.

Hopefully, this post contains some additional ideas, even if it does not contain DAX, M, or SQL code.

Regards,

Tom

[michaelsh · Accepted solution]

Ok, I think I have a solution...

Without Power Query/M "Account to use" (@TomMartens )

and without having to manage multiple parents in the accounts hierarchy ( @ImkeF )

Actually it is a pretty simple solution, so maybe I am missing something and will be glad if you could write your opinion on this.

So the basic idea is this:

1. Create a second, inactive relationship between accounts and the balance sheet hierarchy, based on a new calculated column that tells what hierarchy to use if an account is in Debit... (thanks David Grinshtein for this idea, hope he will soon open an account in the community)

2. Create a measure that breaks the balance calculation into 3 parts:

(1) Calculate regular balances for non-Account Payable acccounts - using a regular relationship...

(3) Calculate only balances for Account Payable accounts that are Credit - again - using a regular relationship...

      AP Balance Credit Only = SUMX(Accounts,if([AP Balance]<0,[AP Balance]))

(5) Calculate only balances for Account Payable accounts that are Debit - but this time - USERELATIONSHIP based on our new calculated column
     AP Balance Debit Only = SUMX(Accounts,if([AP Balance]>0,[AP Balance]))
     AP Balance Debit Only USERELATIONSHIP =CALCULATE([AP Balance Debit Only],USERELATIONSHIP(Accounts[Balance Sheet Hier if Debit],'Balance Sheet Hierarchy'[Lev3]))

I use SUMX with IF to dynamically check for Credit/Debit balance on the account level... This also lets to correctly aggregate the results to hierarchy levels

Then, I just add measures 1+3+5. (measures 2,4 are just steps in between)

You can see the solution in the link

https://1drv.ms/u/s!AoP_9ampPIT79AOSYOfVUAhC1fgf?e=oHaNLv

Did I miss something?

[TomMartens]

Hey @michaelsh ,

thanks for sharing your solution.

I'm wondering about the values shown for February onwards for the account 312 and lvl 3: AP-foreign, but I'm not a balance expert:

and the lvl 3

Nevertheless, thanks for sharing your solution.

Do not forget to mark your solution as an answer as it will help others.

Regards,

Tom

[michaelsh]

@TomMartens 

Thank you very much for checking the Year-Month attribute.

In my example - in February there was a 100 credit transaction (vendor invoice) for "312 - Vendor 2"

So since February the vendor balance is -50 (credit) (50-100=-50), so it returns to be show in it's regular "Accounts Payable" hierarchy. And all the above levels sum up the relevant data.

So it is exactly what I was trying to achieve - to create a measure that "shows" the account balance in different hierarchies, dynamically calulating the condition.

Thanks a lot!

[ImkeF]

Hi @michaelsh ,

that's a really nice DAX - solution!

Usually, I try to avoid CallbackIDs that prevent caching and are often introduced by if-statements. But your solution has the advantage of using less model size.  

So again: It depends 😉

[daxer-almighty]

To do this you have to:

1. Make sure that the hierarchy in the underlying table accommodates all your scenarios all in one go. You cannot dynamically move levels.

2. Create a DAX measure that calculates correctly and returns BLANK when necessary. This will in effect hide the entry you don't want to see.

Doable but not an entry-level design and calculation.

[amitchandak]

@michaelshparber , refer if this can help

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[michaelshparber]

@amitchandak this is a different scenario and doesn't help

Any relevant help would be much appreciated

Thank you

[Greg_Deckler]

@michaelshparber - I downloaded your PBIX file and am trying to make certain that I understand what you are looking to do. To state this in words, you want to move Account Primary Key (Account PK) 312 (312 - Vendor 2) transactions to not appear in the balance hierachy for Liabilities, Accounts Payable, AP foreign. Correct?

Following the principle of least changes, I edited Balance Amt as follows:

Balance Amt = 
    VAR __Lev1=MAX('Balance Sheet Hierarchy'[Lev1])
    VAR __Lev2=MAX('Balance Sheet Hierarchy'[Lev2])
    VAR __Lev3=MaX('Balance Sheet Hierarchy'[Lev3])
RETURN
    IF(__Lev1="Liabilities" && __Lev2="Accounts Payable" && __Lev3="AP - foreign",
    CALCULATE(Transactions[Transactions Amt],FILTER(all('Calendar'),'Calendar'[Date]<=max('Calendar'[Date])),Accounts[Account PK]<>"312"),    
    CALCULATE(Transactions[Transactions Amt],FILTER(all('Calendar'),'Calendar'[Date]<=max('Calendar'[Date]))))

However, this seems to break the second visual, so not entirely certain what is going on with that one but step 1 complete! 🙂 @ me if you want me to try to fix the second visual or perhaps you understand your logic there better than I and can fix it.

[michaelsh]

Thanks @Greg_Deckler  for trying to help

Here is what I want as a result: 

If any vendor's balance is positive then show it in Advances to suppliers in Assets (as shown with arrow)

But if the balance is negative - then show it where it is defined by a regular relationship (green V in 2020-02).

Any help is very much appreciated!

[Greg_Deckler]

@michaelshparber - Huh, well I definitely didn't get that from the original post!

[ImkeF]

Hi @michaelshparber ,

you didn't find this on my blog because this is what my clients are paying me for 😉

But maybe it's time to release some of it...

My solution lies mainly in data modelling. Then you can use this measure for your balance sheet figures:

BalanceImke =
SUMX (
    FILTER (
        CROSSJOIN ( VALUES ( Imke[Tipping] ), VALUES ( 'Calendar'[Date] ) ),
        SIGN ( [Transactions Amt] ) <> Imke[Tipping]
    ),
    [Transactions Amt]
)

You have to change the Hierarchy-table to this and use bidirectional filtering:

So I'm including the "Tipping Accounts" in both groups/hieararchies and then use the DAX measure to decide where to use their amounts at the end.
Blogpost to follow 😉
Enclosing file as well.

[michaelsh]

Thanks a lot, @ImkeF !

I will check your measure later today and will let know how it works.

Does your solution require that ALL the vendor accounts will have to appear twice in the hierarchy?

I mean, I don't know beforehand which vendor will have a Debit balance. Now it is 312, but tomorrow it can be 311, 313...

That could be a problem since:

1. I like the simple hierarchy where all the vendors are defined once under AP. I have other uses for this simple "streightforward" hierarchy.

2. I have many vendors and this will make my table big.

I do feel that this can be solved with just a measure.

I have done this with another model but there I had a disconnected hierarchy.

Now I want to do it with relationship because I believe it will work faster.

I just need this "small" exception in my measure.

SUMX that will:

1. Check that the account is "Accounts Payable" in the original hierarchy

2. If the balance sign is positive that it will not sum this account in the original hierarchy

3. It will sum these positive accounts if current hierarchy is "Advance to suppliers" / Assets (and will ignore the relationship)

I believe it can be done just with measure, without having double accounts in the hierarchy.

What do you think?

[ImkeF]

Hi @michaelsh ,

yes, all vendors have to be allocated twice (it's been late when I posted it yesterday...)

The beauty of this solution is that you can still use your original hierarchy, even with the new measure. It will then return all vendors in liabilities ( as this can be required sometimes as well).

The measure is fairly fast and doesn't have CallbackIDs, so I don't think that you will be able to speed this up with a pure formula-based-approach. How many vendors do you have? Couple of thousands or a couple of millions?

But I'm no DAX wizard, for sure - so will leave this exercise for others.

@Greg_Deckler , @TomMartens : Any take on this?

[ImkeF]

Hi @michaelsh ,

if permance is an issue, it's better to switch to single relationship and create a dedicated "Balance with Tipping" - measure that uses this base measure for bridging instead: 

Transactions Amt Tipping = CALCULATE(SUM(Transactions[Amt]), Imke)

 Attaching file as well.