Need help with Dax If statement including a measure

[ekelly]

We are trying to create a green/red stacked bar report relating our inventory and sales.  Sales is our starting point and the green/red status is a relationship of inventory to sales.  For example, if we have 1,000 units in sales and 400 units in stock, 400 would be green (as these are in stock) and 600 would be red (as we need more units to complete the sale).  This we have working in Power BI.  It is the scenario for when we have more stock than sales that we cannot cap the green at our sale.  For example, if we have 1,000 units in sales and 2,000 units in stock, we want green to be 1,000 (because we don't care we have excess) and red would be 0 (as nothing needs to be produced to meet the sale).

Our issue is that our sales data is a direct metric from our data source, but the inventory is a new measure using the below formula.  This is due to our data having the inventory for an item stated multiple times (when it is a unique instance to that item) and this metric should not be summed.   

=SUMX (DISTINCT([Item Number]), FIRSTNONBLANK ([On Hand]), 0)

What we are trying to create two new measures to drive our green and red visual. 

Green would be....If Inventory >=Sales then bring back Sales metric

Red would be... If Inventory <Sales then subtract Inv from Sales

We are having issues with Dax since our Inventory is already a measure and our knowledge of Dax is minimal. 

[alexei7]

Hi @ekelly,

It'd be helpful to understand what you're trying to achieve if you could share some sample data (or even better, a pbix model), obviously removing any sensitive/confidential data.

Thanks

Alex

[ekelly]

Here would be a sample of the chart we are looking to achieve.  Green and Red in the chart would be new measures and then we would create the stacked bar visual with these new measures.

 

[ekelly]

The complexity is that inventory in stock is already a new measure using the below formula from our data source.  I cannot share the actual pbix, as it does contain confidential information.

=SUMX (DISTINCT([Item Number]), FIRSTNONBLANK ([On Hand]), 0)

[v-juanli-msft · Accepted solution]

Hi @ekelly

Create two measures

available = IF(MAX([Sales])<[Inventory in Stock],MAX([Sales]),[Inventory in Stock])

need = IF(MAX([Sales])>[Inventory in Stock],MAX([Sales])-[Inventory in Stock],0)

Best Regards

Maggie