Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
haputhanthree
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

haputhanthree_0-1642229788811.png

Expectecd result.

haputhanthree_1-1642229815739.png


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

1 ACCEPTED SOLUTION
ValtteriN
Super User
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]))
 
ValtteriN_0-1642254642566.png

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!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
ValtteriN
Super User
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]))
 
ValtteriN_0-1642254642566.png

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!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.