Forum Discussion

Cuscino's avatar
Cuscino
Frequent Visitor
8 years ago

DAX Formula Compare rows

Hi

In the table below I have

 

Qty Price = DIVIDE(sum(Table A [Inv Value EUR]);SUM(Table A[fN Inv Qty]))

 

Cost of Material = IF(SUM(Table B[Open quantity])>SUM(Table A [fN Inv Qty]); sum(Table A[fN Inv Value EUR]);[Qty Price]*sum(Table B[Open quantity]))

 

Total Qty= IF(sum(Table B[Open quantity])>SUM(Table A[fN Inv Qty]);sum(Table B[Open quantity]);SUM(Table A[fN Inv Qty]))

 

So the problem is I m getting the Total wrong, I know that there is some problem in the formula but I don t know how to resolve it

 

Moreover, I would like that when I have a blank cell that was considered as 0

 

(in the Example I should have as Tot Cost of Material 29767,16 €)

 

Thanks

Need Help
Show and Tell

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    Hi Cuscino，

     

    Have you created a relationship of the two tables A and B?

     

    If it is convenient, could your share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? 

     

    In addition, you could use If function to set a blank cell to be 0 like the formula below.

     

    If ( isblank(),0,...)

     

    Best Regards，

    Cherry

    • Cuscino's avatar
      Cuscino
      Frequent Visitor
      8 years ago

      So

       

      I have Table B, where I have all the Sales Order, Site of the warehouse, with multiple Items and the Open Qty

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Table A is jsut the Inventory so Items, Inv Qty and the € Values

       

      Then there is a Connection Table (Table C) between this two table, where we have all the Items 

       

      I m trying to compare this two tables in one unique visualization 

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      The formula in the table are working fine, the problem is the Totals

       [Cost of Material ] = IF(SUM(Table B [Open quantity])>SUM(Table A [fN Inv Qty]);sum(Table A[fN Inv Value EUR]);Qty Price*SUM(Table B[Open quantity]) 

       

       Here is basically using the Total instead of each single row and that´s why the Total isn´t right

       

      Total Qty= IF(sum(Table B[Open quantity]) > SUM(Table A[fN Inv Qty]); SUM(Table A[fN Inv Qty]);sum(Table B[Open quantity])) 

       

      I´m trying also to create a Slicer that indicates where we have Inventory or shortage, so If Open Qty > Inventory then Shortage otherwise Inventory. 

       

      Thanks