Count the Number of Invoice Cancelled and Trade Return

[ADSL]

Hi BI Community Team,

I would need to track the invoice cancelled and trade return by sales rep.

In sales order file, we have a column called [BillingType], it has 02 values --- [ZF2] refer to "Order", [ZRE] refer to "Cancel or Return"

So then all sales order return/cancelled are selected/choosed with different reason as the following below.

Any suggestion of the metric/calculation to deliver as the result below?

Source file: https://drive.google.com/drive/folders/1umYKZ-RHoDk42N-kjpvQViDFaipQLhKm?usp=sharing 

Best Regards,

[lukiz84 · Accepted solution]

Cancelled = 
   VAR cancelReasons = 
      CALCULATETABLE(
         VALUES(Reason[HeaderReasonCode]),
         Reason[Reason] = "Cancelled"
      )
   RETURN CALCULATE(
      DISTINCTCOUNT(Orders[InvoiceNo]),
      TREATAS(cancelReasons, Orders[HeaderReasonCode])
   )

Trade Return = 
   VAR cancelReasons = 
      CALCULATETABLE(
         VALUES(Reason[HeaderReasonCode]),
         Reason[Reason] = "Trade Return"
      )
   RETURN CALCULATE(
      DISTINCTCOUNT(Orders[InvoiceNo]),
      TREATAS(cancelReasons, Orders[HeaderReasonCode])
   )

Total No. Invoice =
   DISTINCTCOUNT(Orders[InvoiceNo])

Other Reason = 
   CALCULATE(
       DISTINCTCOUNT(Orders[InvoiceNo]),
       Orders[HeaderReasonCode] <> "" && 
       NOT(Orders[HeaderReasonCode] IN VALUES(Reason[HeaderReasonCode]))
   )

[ADSL]

Hi @lukiz84,

I am a new BI learner, so then could you please help to support to provide the final measure for:

1. Cancelled

2. Trade Return

3. Other Reason

4. Total No. Invoice

Thanks and Regards,

[lukiz84 · Accepted solution]

Cancelled = 
   VAR cancelReasons = 
      CALCULATETABLE(
         VALUES(Reason[HeaderReasonCode]),
         Reason[Reason] = "Cancelled"
      )
   RETURN CALCULATE(
      DISTINCTCOUNT(Orders[InvoiceNo]),
      TREATAS(cancelReasons, Orders[HeaderReasonCode])
   )

Trade Return = 
   VAR cancelReasons = 
      CALCULATETABLE(
         VALUES(Reason[HeaderReasonCode]),
         Reason[Reason] = "Trade Return"
      )
   RETURN CALCULATE(
      DISTINCTCOUNT(Orders[InvoiceNo]),
      TREATAS(cancelReasons, Orders[HeaderReasonCode])
   )

Total No. Invoice =
   DISTINCTCOUNT(Orders[InvoiceNo])

Other Reason = 
   CALCULATE(
       DISTINCTCOUNT(Orders[InvoiceNo]),
       Orders[HeaderReasonCode] <> "" && 
       NOT(Orders[HeaderReasonCode] IN VALUES(Reason[HeaderReasonCode]))
   )

[lukiz84]

Hi,

Cancelled =
   CALCULATE(
      COUNT(ReasonTable[Reason]),
      ReasonTable[Reason] = "Cancelled"
   )

Trade Return =
   CALCULATE(
      COUNT(ReasonTable[Reason]),
      ReasonTable[Reason] = "Trade Return"
   )

Total No. Invoice =
   COUNT(ReasonTable[Reason])

with 3 measures...

[ADSL]

Hi @lukiz84,

Thank you for your feedback.

In sales order, it doesn't have reason and only have "HeaderReasonCode" and "HeaderReasonDesc".

For screenshot above, it's a master of reason that group into 02.

Please kindly help to check.

Best Regards,

[lukiz84]

Ah ok, I understand.

Cancelled = 
   VAR cancelReasons = 
      CALCULATETABLE(
         VALUES(Reason[HeaderReasonCode]),
         Reason[Reason] = "Cancelled"
      )
   RETURN CALCULATE(
      DISTINCTCOUNT(Orders[InvoiceNo]),
      TREATAS(cancelReasons, Orders[HeaderReasonCode])
   )

Total No. Invoice =
   DISTINCTCOUNT(Orders[InvoiceNo])

For the Trade return, change the first measure accordingly

[ADSL]

Hi @lukiz84,

May I need your help again of measure? In case, the reason list is not update to date then we're missing. We want to move it into another group like "Other Reason" as the screenshot below.

After see the result then we know the reason is not update to update or have a new reason in sales order.

Thanks and Regards,

[lukiz84]

Other Reason = 
   CALCULATE(
       DISTINCTCOUNT(Orders[InvoiceNo]),
       Orders[HeaderReasonCode] <> "" && 
       NOT(Orders[HeaderReasonCode] IN VALUES(Reason[HeaderReasonCode]))
   )

[ADSL]

Hi @lukiz84,

Thank you very much for your quick respond & helpful measure.

Best Regards,

[ADSL]

Hi @lukiz84,

I notice that your measure is modified, any issue or concern? becuase it's different for "Cancelled".

[lukiz84]

It didn't work, that's why I changed it. Please use the one which is there now... (in the edited post)

[ADSL]

Hi @lukiz84,

Well noted with thanks.

Sorry...if we follow the same reason --- Cancelled, Return & Other. But we calculate it to Amount that column name is [Amount No VAT]. 

Any suggestion of metric? 

Thanks and Regards,

[lukiz84 · Accepted solution]

Just replace the DISTINCTCOUNT with SUM(Orders[Amount No VAT]) in each Measure

[ADSL]

Hi @lukiz84 ,

I followed your advise, but only Trade Return Amount that it's not working correctly when mapping with manaul in excel.

Result ---- Trade Return of No. Invoice is correct, actually amount is -21.3

If you want to check the source, pls. go to this link - https://drive.google.com/drive/folders/1umYKZ-RHoDk42N-kjpvQViDFaipQLhKm?usp=sharing 

[lukiz84]

Amount is -61,30 in your source. And that's what DAX calculates for me if i test it?

[ADSL]

Hi @lukiz84,

Pls. filter the BillingType, and choose "ZRE". It's cancelled/return. 

Actual Result of Amount:

1. Cancelled Amount = -112,2

2. Trade Return Amount = -21.3

3. Other Amount = -40

[lukiz84]

But in your Source, ZPB is "Trade Return", see

[ADSL]

Hi @lukiz84,

Ok, I see now. I had updated the file but sharing was not update it.

Thanks and Regards,