Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
ADSL
Post Prodigy
Post Prodigy

Count the Number of Invoice Cancelled and Trade Return

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.

 

2022-10-06_15-40-34.jpg

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

 

2022-10-06_15-48-32.jpg

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

 

Best Regards,

 

2 ACCEPTED SOLUTIONS
lukiz84
Memorable Member
Memorable Member

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]))
   )

View solution in original post

lukiz84
Memorable Member
Memorable Member

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

View solution in original post

17 REPLIES 17
ADSL
Post Prodigy
Post Prodigy

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
Memorable Member
Memorable Member

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
Memorable Member
Memorable Member

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...

 

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
Memorable Member
Memorable Member

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

 

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.

 

2022-10-06_16-47-49.jpg

Thanks and Regards,

lukiz84
Memorable Member
Memorable Member

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

Hi @lukiz84,

 

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

 

2022-10-06_17-04-53.jpg

Best Regards,

Hi @lukiz84,

 

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

 

2022-10-06_17-13-27.jpg

lukiz84
Memorable Member
Memorable Member

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

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]. 

 

2022-10-06_19-22-06.jpg

 

Any suggestion of metric? 

 

Thanks and Regards,

 

 

lukiz84
Memorable Member
Memorable Member

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

Hi @lukiz84 ,

 

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

 

2022-10-06_19-59-58.jpg

 

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

 

2022-10-06_20-01-56.jpg

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

lukiz84
Memorable Member
Memorable Member

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

lukiz84_0-1665062138633.png

 

lukiz84_1-1665062279113.png

 

 

Hi @lukiz84,

 

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

 

2022-10-06_20-21-47.jpg

 

Actual Result of Amount:

 

1. Cancelled Amount = -112,2

 

2. Trade Return Amount = -21.3

 

3. Other Amount = -40

lukiz84
Memorable Member
Memorable Member

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

 

lukiz84_0-1665063168682.png

 

Hi @lukiz84,

 

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

 

Thanks and Regards,

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.