CODEWITHSUNDEEP
powerbipowerquery
Lauro Bicalho
Lauro Bicalho

Reputation: 1

Getting measure to only show positive values in matrix Power BI

I'm new to DAX. Looking for your expertise. Looked all over the web but couldn't find a proper solution.

I have a matrix that shows several factories and it's inventories, the rows being factory and SKU. I've created a measure that's the difference between two other measures. The overall idea is: I have a inventory quantity and an orders volume. I defined a "balance" measure that's inventory - sales. That is, what is still left for sale.

Normally it works fine, but when I have a negative value in balance, things get messed up. The subtotal in the factory row sums all the numbers, including negatives, giving me a balance subtotal that is smaller than it's supposed to be. I can't count the negatives. So I've tried to solve this by defining the measure as

Balance = IF([Inventory]>[Sales],[Inventory] - [Sales],BLANK())

Now this works fine visually to display only the positive values in balance, but still sums up the negatives in the factory subtotal.

How can I make this measure to only show and sum up the positives?

I appreciate your help.

P.S.: the inventory and sales measures are basically SUM's of different tables for simplicity and understanding by my part

Matrix with measures. The columns are "Estoque" as inventory, "Embarque programado total" as sales and "Saldo Disp. Venda" as the balance

Upvotes: 0

Views: 6119

Answers (2)

Lauro Bicalho
Lauro Bicalho

Reputation: 1

As it turns out, I was able to find a solution through trial and error. I changed my measure to (FatoEstoque is the table that contains the inventory numbers):

Balance= CALCULATE([Inventory]-[Sales],FILTER(FatoEstoque,[Inventory]>[Sales]))

Although I'm not even sure why it works (lol), it works! So I'm not complaining. This measure only sums up the positive values, showing the correct subtotal as I needed.

Filtering the Inventory table to values where [Inventory]>[Sales] works, but adding another filter with the Sales table and the same condition don't.

Here is an image comparing the results. The new measure is called SaldVender2

Solution

Upvotes: 0

DEEPAK LAKHOTIA
DEEPAK LAKHOTIA

Reputation: 993

Create a measure with the below code.

Balance = CALCULATE(SUM([Inventory]) - SUM([Sales]), FILTER(Table1, [Inventory]>[Sales]))

Upvotes: 0

Related Questions