Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello
I have a data set with tons of fields from customer no to destination, product no, name, seller name, product type, segment, packaging type etc. for profitability report
What i want to do is to create a report for customers only:
My attention is to show off customers or productss if they are above or below profit margin % (FP2)
For Profit margin (FP2) % i created below measure:
FP2% = CALCULATE(DIVIDE([FP2];SUM(OK_INVOICE_ALL_EXCEL[Turnover PV])))
and i also created profit margin % for all company average as below;
FP2 % all = CALCULATE(DIVIDE(OK_INVOICE_ALL_EXCEL[FP2];SUM(OK_INVOICE_ALL_EXCEL[Turnover PV]));ALLEXCEPT(OK_INVOICE_ALL_EXCEL;OK_INVOICE_ALL_EXCEL[BU];OK_INVOICE_ALL_EXCEL[Currency Selection];OK_INVOICE_ALL_EXCEL[Only Year];OK_INVOICE_ALL_EXCEL[Fiili/Bütçe];OK_INVOICE_ALL_EXCEL[MONTH]))
so mathematically a customer with 5% FP2% must be below 15% FP2% all
to distinguish and classify that i created a column as below
Threshold FP2 % = IF([FP2%]>[FP2 % all];"Above";"Below")
so this column will help me to show and filter whether customers or products above or below FP2% within company figures
When i applied all those above, i select one customer which has lower FP2% compared to all
It calculates (i guess) all the fields and returns more than one THRESHOLD FP2%
Please help me how can i solve this out. IF Customer has 6.65% FP2 margin and below 16.63% of average, how can i classify this customer as below instead of more than one.
EDIT
I guess i found the problem but dont know the formula;
Customer or Product has more than one different shipment type
for example one with bag one with container. Bag has higher FP2% than all average.
I need to disregard all those shipment type, country, segment, seller name etc. How can i do that and create my table only based on customer name and product name?
Thanks and regards,
Solved! Go to Solution.
Hi Dale,
Thank you for replying
When i apply the formula as you mentioned it calculates all the FP2% all including all the years and months, business units etc. etc.
thus i did "all except" formula with those should not affected
Hi @i_kafali,
Maybe you can modify it like the formula of "FP2 % all".
FP2% = CALCULATE ( DIVIDE ( [FP2]; SUM ( OK_INVOICE_ALL_EXCEL[Turnover PV] ) ); ALL ( OK_INVOICE_ALL_EXCEL[shipment type] ) )
Best Regards,
Dale
Hi Dale,
Thank you for replying
When i apply the formula as you mentioned it calculates all the FP2% all including all the years and months, business units etc. etc.
thus i did "all except" formula with those should not affected
Hi @i_kafali,
So you solved it?
Best Regards,
Dale
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
89 | |
81 | |
65 | |
64 | |
60 |
User | Count |
---|---|
170 | |
114 | |
102 | |
73 | |
69 |