Forum Discussion
Excel Formula to DAX
Hi,
I have the following excel formula, that retains the following values;
Allocated
Pending Allocation
Partial Allocation
In Production
IF(OR([status]="Production",[status]="FactShipped"),"In Production",
IF(AND([[delivery_type]]="ship as available",[[order_qty]]=[[confirmed_qty]]),"Allocated",
IF(AND([[delivery_type]]="ship as available",[[confirmed_qty]]>0,[[confirmed_qty]]<[[order_qty]]),"Partial Allocation",
IF(AND([[delivery_type]]="full order consolidation",SUMIF([order_no],[[order_no]],[order_qty])=SUMIF([order_no],[[order_no]],[confirmed_qty])),"Allocated",
IF(AND([[delivery_type]]="full order consolidation",SUMIF([order_no],[[order_no]],[confirmed_qty])>0,SUMIF([order_no],[[order_no]],[confirmed_qty])<SUMIF([order_no],[[order_no]],[order_qty])),"Partial Allocation","Pending Allocation")))))
My efforts are to add a column in the power bi report that would capture the result based on the logic. The excel formula looks at multiple fields to retain the above-mentioned values.
Thanks and Regards,
Jajati Dev
Hi,
I was able to replicate the formula.
Order Status =IF(OR('Orders'[status]="Production",'Orders'[status]="FactShipped"),"In Production",IF(AND('Orders'[delivery_type]="Ship as available",'Orders'[order_qty]='Orders'[confirmed_qty]),"Allocated",IF('Orders'[delivery_type]="Ship as available" && 'Orders'[confirmed_qty]>0,"Partial Allocation",IF('Orders'[delivery_type]="Full order consolidation" && SUMX(FILTER('Orders','Orders'[order_no]=EARLIER('Orders'[order_no])),'Orders'[order_qty])=SUMX(FILTER('Orders','Orders'[order_no]=EARLIER('Orders'[order_no])),'Orders'[confirmed_qty]),"Allocated",IF('Orders'[delivery_type]="Full order consolidation" && SUMX(FILTER('Orders','Orders'[order_no]=EARLIER('Orders '[order_no])),'Orders'[confirmed_qty])>0 ,"Partial Allocation","Pending Allocation")))))Now the next step for me is to code this in M Language.Thanks,Dev
6 Replies
Hey JajatiDev ,
Power BI is not Excel.
What do you want to do? Add column? Create a measure? What should the SUMIF represent? The sum on a row level? The sum for a specific column?
Please tell us how your table looks like and what you want as a result and then it's easier to help you.
Best regards
Denis
Hi,
Yes, I do acknowledge Power BI is different from Excel.
All that I am trying to achieve is to add a column in the report replicating the excel formula to retain the text value.
Thanks and Regards,
Jajati Dev
Why don't you create two measures for the two SUMIFs using CALCULATE?
Then you could just include them in the column/measure you want to build.
Hi,
I am not calculating anything here. The SUMIF you see are part of the logic statement to retain a text at the end.
The logic is meant to retain the following;
Allocated
Pending Allocation
Partial Allocation
In Production
This logic in excel looks at every table row of the specified fields to provide a consolidated order status.
I'm guessing you want a calculated column?
Do you have different tables? SUMIF seems to indicate at least two? If yes, why not connect them using ORDER NUMBER? Then you can just apply an IF(RELATED...).
Hi,
I was able to replicate the formula.
Order Status =IF(OR('Orders'[status]="Production",'Orders'[status]="FactShipped"),"In Production",IF(AND('Orders'[delivery_type]="Ship as available",'Orders'[order_qty]='Orders'[confirmed_qty]),"Allocated",IF('Orders'[delivery_type]="Ship as available" && 'Orders'[confirmed_qty]>0,"Partial Allocation",IF('Orders'[delivery_type]="Full order consolidation" && SUMX(FILTER('Orders','Orders'[order_no]=EARLIER('Orders'[order_no])),'Orders'[order_qty])=SUMX(FILTER('Orders','Orders'[order_no]=EARLIER('Orders'[order_no])),'Orders'[confirmed_qty]),"Allocated",IF('Orders'[delivery_type]="Full order consolidation" && SUMX(FILTER('Orders','Orders'[order_no]=EARLIER('Orders '[order_no])),'Orders'[confirmed_qty])>0 ,"Partial Allocation","Pending Allocation")))))Now the next step for me is to code this in M Language.Thanks,Dev
