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

AreaItemSalesInventoryInventory-Sales
A1144457,1812500-31957,18
 2 215000 
 3486829,7115315000-171829,712
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?

 

 

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

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

 

Hope this helps.

 

 
az38
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]))
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
LinkedIn
cpearson
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:

AreaItem
AA1
AA2
BB1

 

I would expect the sum of both negative values 🙂

Helpful resources

Announcements
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!