cancel
Showing results for
Did you mean:
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:

 Area Item Sales Inventory Inventory-Sales A1 1 44457,18 12500 -31957,18 2 215000 3 486829,7115 315000 -171829,712 Result 531286,8915 542500

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?

4 REPLIES 4
Responsive Resident

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(filter('ItemMaster', 'ItemMaster'[Inventory]<ItemMaster[Sales]), [Inventory_Sales])`

Hope this helps.

Super User

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]))
RETURN
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.

Post Prodigy

Hey @cpearson Thanks for your support!

I just mean there are two granularities like this:

 Area Item A A1 A A2 B B1

I would expect the sum of both negative values 🙂

Announcements

#### The Power BI Community Show

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

#### Charticulator Design Challenge

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

#### Check it Out!

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