Electronic Reporting ( calc. Field)

Finance and Operations/AX Developer Forum
1

Hi
i want to create a ER as item , sls qty, prod qty, etc
for this
i am taking the datasource as InventTrans and i want to get the vlaues of Sales , prod, purhcase etc based on transorigin.ReferenceCategory (for exmaple if categoy is zero it is sales)
i am trying to create a calculated field with below formula (where @ is the inventtrans table)

1.) my question is how to sum the qty based on different categories
2.) do i have to create different calc fields for Prod and purch etc.,
3) when iam trying to create Calc field like below iam getting error

`*

if(@.‘InventTransOrigin.ReferenceCategory’==0,@.Qty,0)

*`

Syntax error: We found a problem with this formula; syntax error near ‘(’

2

To create a calculated field in Power BI that sums the quantity based on different categories, you can use the SUMX function combined with an IF statement. Here’s an example formula that you can use:

Sales Qty = SUMX(FILTER(InventTrans, InventTrans[InventTransOrigin.ReferenceCategory] = 0), InventTrans[Qty])

This formula uses the SUMX function to sum the quantity values for all rows in the InventTrans table where the ReferenceCategory is equal to 0 (indicating a sales transaction). You can modify this formula to calculate the values for other categories (such as production or purchase) by changing the value in the IF statement to the appropriate category code.

Regarding your questions:

  1. To sum the quantity based on different categories, you can use the SUMX function combined with an IF statement, as demonstrated in the formula above.
  2. You will need to create a separate calculated field for each category that you want to calculate. You can modify the formula for each field to calculate the values for the appropriate category.
  3. The error you are seeing is due to the use of single quotes around the column name in your formula. Instead of using single quotes, you should use double quotes or no quotes at all. Here’s the corrected formula:

Sales Qty = IF(InventTrans[InventTransOrigin.ReferenceCategory] = 0, InventTrans[Qty], BLANK())

This formula uses the IF function to check if the ReferenceCategory is equal to 0, and if so, returns the Qty value. If not, it returns a blank value.