Showing results for 
Search instead for 
Did you mean: 
Post Prodigy
Post Prodigy

False result for calculation on lower granularity

Hello everybody.

I have a simple calculation of [Inventory]-[Sales]. I need the result if it is negative otherwise, there should be a blank.

In the ItemMaster there are two levels: Area and Item.

My current approach is not working. It shows me no result on Area-level - as you can see:

 2 215000 
Result 531286,8915542500



The function is:


=IF(SUMX('Item Master';[Inventory])-SUMX(Sales;[Sales])<0;SUMX('Item Master';[Inventory])-SUMX(Sales;[Sales]);BLANK())



Does someone know where my mistake is?



Responsive Resident
Responsive Resident

Hi  @joshua1990 


The filter context on the Result line is the whole table. Totals are not the sum of the indivudal lines, but are calculated according the filter context. 


So if you make sure that your filter context only filters the negative values then the result line will show that value. 


I created two measures under the assumption that the values are in one record (otherwise you need to adjust the measures): 

Inventory_Sales = SUM(ItemMaster[Inventory]) - Sum(ItemMaster[Sales])

Inventory_Sales Negatives Only = SUMX(

2020-01-05 22_14_45-sample tables - Power BI Desktop.jpg


Hope this helps.


Super User
Super User

Hi @joshua1990 

try to use calculate for correct row context usage and maybe SUM instead of SUMX

for example

Measure =
var Expression = CALCULATE(SUM('Item Master'[Inventory]))-CALCULATE(SUM(Sales[Sales]))
IF (Expression < 0;Expression; BLANK())

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
Frequent Visitor

Hi Joshua1990


Can you just clarify what you mean regarding your two levels, I don't understand, do you mean there are two types of record in the data table? Could you include a screen shot of the data?


Are you expecting a value in the Inventory-Sales column against the Result row? It's blank because it's not a negative number.



Hey @cpearson Thanks for your support!

I just mean there are two granularities like this:



I would expect the sum of both negative values 🙂

Helpful resources

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!