Forum Discussion

haputhanthree's avatar
haputhanthree
Frequent Visitor
4 years ago
Solved

Define row values based on other rows in hierarchy

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 eq...
  • ValtteriN's avatar
    ValtteriN
    4 years ago

    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!

ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
4 years ago

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!

haputhanthree's avatar
haputhanthree
Frequent Visitor
4 years ago

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