Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi,
Here is one way to do this:
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!
Hi,
Here is one way to do this:
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |