How to sum only positive values along hierarchy

[mmittermayr]

Hi,

I become desperate after several days getting my head around a solution for this DAX problem. My aim is to filter an aggregated measure with a greater than condition along a flatten hierarchy.

Account Table:

Account	City	Country
100_Tax	New York	USA
200_Sales	New York	USA
300_Salary	New York	USA

Account Hierarchy:
Country (=Root Level)

Region (=Element)

City (=Element)

Account (=Lowest Level)

Now, I have the following table data.

Account	Amount
100_Tax	20
200_Sales	-10
300_Salary	30

I created a simple SUM(Amount) Measure. Let's call it "Measure A". When I use a Matrix Table or Pivot Table I get the following result:

Account Hierarchy	Measure A
USA	40
New York	40
100_Tax	20
200_Sales	-10
300_Salary	30

How can I filter my Measure that I only sum up positive values and replace negative values with zero? Eventuelly I try to get the following result

Account Hierarchy	Measure X	Measure A
USA	50	40
New York	50	40
100_Tax	20	20
200_Sales	-10	-10
300_Salary	30	30

How can I build Measure X? I tried a lot with ISINSCOPE or with SUMMARIZE but nothing worked out..

I appreciate any ideas!

Thank you!

[Greg_Deckler]

@mmittermayr - Well, not sure I am following completely but generally:

Measure =
VAR __Sum = SUMX(FILTER('Table',[Amount]>0),[Amount])
RETURN
IF(ISBLANK(__Sum),0,__Sum)

[mmittermayr]

Thanks @Greg_Deckler .. I tried this, but for some reason it adds up completely different numbers. Maybe it is because my Amount values are actually calculated with TOTALYTD expression.

[Greg_Deckler]

@mmittermayr - Oh yeah, yeesh, see time "intelligence" stuff below. But, sounds like we need to backup a bit. Sample source data and expected output from sample source data and the formulas you use. Could be a measure aggregation issue though, see my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

In your case, SUMX,

Otherwise, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Also, my thoughts on time "intelligence" You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...