Forum Discussion

ekelly's avatar
ekelly
New Member
8 years ago
Solved

Need help with Dax If statement including a measure

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. 

 

  

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    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 

4 Replies

  • alexei7's avatar
    alexei7
    Icon for Continued Contributor rankContinued Contributor
    8 years ago

    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's avatar
      ekelly
      New Member
      8 years ago

      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's avatar
        ekelly
        New Member
        8 years ago

        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)