March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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,
Solved! Go to 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]))
)
Just replace the DISTINCTCOUNT with SUM(Orders[Amount No VAT]) in each Measure
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,
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]))
)
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,
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.
Thanks and Regards,
Other Reason =
CALCULATE(
DISTINCTCOUNT(Orders[InvoiceNo]),
Orders[HeaderReasonCode] <> "" &&
NOT(Orders[HeaderReasonCode] IN VALUES(Reason[HeaderReasonCode]))
)
Hi @lukiz84,
I notice that your measure is modified, any issue or concern? becuase it's different for "Cancelled".
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].
Any suggestion of metric?
Thanks and Regards,
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.
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
Amount is -61,30 in your source. And that's what DAX calculates for me if i test it?
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
But in your Source, ZPB is "Trade Return", see
Hi @lukiz84,
Ok, I see now. I had updated the file but sharing was not update it.
Thanks and Regards,
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
92 | |
91 | |
79 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
72 | |
55 |