cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jeti
New Member

Sum to higher level in hierarchy - unexpected results

Hi,

I had to port a solution from SSAS multidimensional to PowerBI. Unfortunately, it involves many to many relations and parent-child hierarchies:).

 

I don't get correct sum for the higher level for a measure (first clumn in example below), which shows only positive amounts.

image.png

First column - actually a measure is calculated from second column (also a measure) :  IF ([ZnesekIzkazPredznakSum] > 0;[ZnesekIzkazPredznakSum];BLANK())
On the leaf level things are ok, but on the parent level, the negative value is included.

Any ideas?

I can also post the Power BI file.

 

Cheers, Janez

 

 

 

 

2 REPLIES 2
Sean
Community Champion
Community Champion

@jeti

What you've writen can actually work but as a new COLUMN and if it references the column

New Column (Only > 0) = IF ('Table'[Column] > 0, 'Table '[Column], BLANK() )

To make it work as a MEASURE you need to filter out those numbers from the calculation like this...

SUM Only > 0 MEASURE =
CALCULATE ( SUM ( 'Table'[Column] ), FILTER ( 'Table', 'Table'[Column] > 0 ) )

Your measure first performs the sum and then looks at the context!

That's why it works only on the lowest level!

When it moves to the higher level - first it sums all the numbers at that level - and then checks if >0

 

Hope this helps! Smiley Happy

Thanks for reply, I'm new to tabular and DAX,please bear with me 🙂

In my case I have added a series of measures, because ob M2M relationship and attributes in the intermediate table which affect calculation in fact table.  So I came to final calculation by steps.  But only at the final step I want to check for negative amounts.  Using your proposed solution would filter underlying negative rows ...

I tried to convert this last measure (before checking for negative) to column, but I get an out of memory error. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!