Forum Discussion
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 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.
Hi,
Here is one way to do this:TotalToZero =var test = CALCULATE(SUM(TotalToZero[Value]),NOT(CONTAINSSTRING(TotalToZero[Subaccount],"total")),ALL(TotalToZero[Subaccount]))returnIF(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!
2 Replies
- ValtteriN
Community Champion
Hi,
Here is one way to do this:TotalToZero =var test = CALCULATE(SUM(TotalToZero[Value]),NOT(CONTAINSSTRING(TotalToZero[Subaccount],"total")),ALL(TotalToZero[Subaccount]))returnIF(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!
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
)
)
)
returnResult
