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 ...
Need Help
Show and Tell
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