topic Define row values based on other rows in hierarchy in DAX Commands and Tips

Define row values based on other rows in hierarchy

haputhanthree — Sat, 15 Jan 2022 07:06:06 GMT

I have hierarchical data structure and Account has Sub account naming total and detail, where total always has a value and detail sub accounts may have values. When detail rows have a value sum is equal to total value of each account.
I want to have a Dax measure when details rows have a value replace total row value with 0.

Original

Expectecd result.

Herewith attached sample pbix file for reference.
Thank you in advance.

Re: Define row values based on other rows in hierarchy

ValtteriN — Sat, 15 Jan 2022 13:50:48 GMT

Hi,

Here is one way to do this:

TotalToZero =

var test = CALCULATE(SUM(TotalToZero[Value]),NOT(CONTAINSSTRING(TotalToZero[Subaccount],"total")),ALL(TotalToZero[Subaccount]))

return

IF(test>0,

CALCULATE(SUM(TotalToZero[Value]),

filter(TotalToZero,NOT(CONTAINSSTRING(TotalToZero[Subaccount],"total")))),

SUM(TotalToZero[Value]))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Re: Define row values based on other rows in hierarchy

haputhanthree — Sun, 16 Jan 2022 14:55:41 GMT

ValtteriN

Thank you for the solution.

Update a bit of Original query to get the Main account totals as expected.

TotalToZero =
VAR DetailSum =
CALCULATE(
SUM('Fact Account'[Value])
,'Dim Account'[isTotalAccount] = 0
,ALL('Dim Account'[Sub Account])
)

VAR Result =
IF(
DetailSum > 0 && ISFILTERED('Dim Account'[Account])
,CALCULATE(
SUM('Fact Account'[Value])
,filter(
'Dim Account'
,'Dim Account'[isTotalAccount] = 0
)
)
,CALCULATE(
SUM('Fact Account'[Value])
,filter(
'Dim Account'
,'Dim Account'[isTotalAccount] = 1
)
)
)
return

Result