I don't understand why a stock graph isn't categorizing by deposit

[jfat]

I have a Stock table called "S Stock" in which I have the column [tipodeposito] (which is the type of deposit) and [deposit] (which is the deposit per se). I created a Stock measure that only took into account some of the types of deposits and deposits. However, when I make a table in the report and categorize it by type of deposit, it shows the entire stock without differentiation. I have made this two ways (keep in mind [cantidad] in quantity):

1.

Stock = CALCULATE(SUM('S Stock'[cantidad]), OR('S Stock'[tipodeposito] = "Consignado", OR( 'S Stock'[deposito] = "ALMACEN PYH", OR ('S Stock'[deposito] = "BODEGA PYH", 'S Stock'[deposito] = "Casa Big Sur"))))

In this case, you should know all those examples of [deposito] are just a share of a [tipodeposito] that is called "Bodega" (which encompasses other deposits and doesn't intersect with "Consignado"). However, when I make the bar graph and try to differentiate the stock by [tipodeposito], this is how it appears:

 

2.

In this case, I created two measures in order to better differentiate the stock I want to take into account from [tipodeposito] = "Consignado" and from [tipodeposito] = "Bodega". So:

Stock Bodega =

CALCULATE(SUM('S Stock'[cantidad]), OR('S Stock'[deposito] = "ALMACEN PYH", OR('S Stock'[deposito] = "BODEGA PYH", 'S Stock'[deposito] = "Casa Big Sur")))

Stock Consignado =

CALCULATE(SUM('S Stock'[cantidad]), 'S Stock'[tipodeposito] IN { "Consignado" })

Later, I sum them up in the Stock measure:

Stock = CALCULATE(Stock[Stock Bodega]) + CALCULATE(Stock[Stock Consignado])

And this is the graph I get (even weirder, as the "Bodega" has all the stock available and "Consignado" just a part of it, them being a simple column which categorizes all the stock in two categories):

How can I have a graph in which the total stock is divided into these two categories, but in which "Bodega" only has those three deposits I mentiones ("ALMACEN PYH", "BODEGA PYH", "Casa Big Sur"), and without using filters in the table, just in the formula?

Thanks!

[uzuntasgokberk]

Hello @jfat,

Category format needs to be column. Measures can't be added into Category. If you want to add it you can use power query transformation or Dax new column.

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

İf this post helps, then please consider Accept it as solution and kudos to help the other members find it more quickly.

[jfat · Accepted solution]

Hi Gökberk,

Thanks for the reply. 

Actually, [tipodeposito] is a column.

However, I could finally find a solution thanks to an unending conversation and iteration with ChatGPT lol. I'll post its final answer here:

Solution:
1. Modify the Stock Bodega Measure: Ensure that the filter is correctly applied to sum only the corresponding values.

DAX
Copiar código
Stock Bodega =
SUMX(
FILTER(
'S Stock',
'S Stock'[deposito] IN { "ALMACEN PYH", "BODEGA PYH", "Casa Big Sur" }
),
'S Stock'[cantidad]
)
2. Modify the Stock Consignado Measure: Ensure that only the values for "Consignado" are summed.

DAX
Copiar código
Stock Consignado =
SUMX(
FILTER(
'S Stock',
'S Stock'[tipodeposito] = "Consignado"
),
'S Stock'[cantidad]
)
3. Modify the Stock Measure: Integrate both measures and calculate the total.

DAX
Copiar código
Stock =
CALCULATE(
[Stock Bodega] + [Stock Consignado]
)
Explanation:
SUMX: Iterates over the rows of the table that meet the given conditions and sums the [cantidad] values.
FILTER: Filters the rows of the table based on the criteria set for [deposito] and [tipodeposito].
CALCULATE: Sums the values of [Stock Bodega] and [Stock Consignado] while respecting the established filters.