cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## 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.

1 ACCEPTED SOLUTION
Super User

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!

Proud to be a Super User!

2 REPLIES 2
Super User

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!

Proud to be a Super User!

Frequent Visitor

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