A tricky logic to modify a field to show sales amount in a report

[nmtinx1]

Dear Expert,

I have a table which contains Customer number, Salesman, Division and YTD Sales.  I would like to create a report  including all columns using the following logics:

Our annual budget is $120K. If the YTD sales is great than the YTD budget, (for example, July YTD budget is $70K = 7 divided by 12 times 120K. User will need to enter the month to run the report), the report will show the actual customer number, salesman, division and YTD sales. if not, the report will change the customer numbers to “Others” and the report will show customer number as “Others” and also group the YTD sales by salesman and division. 

I can manage the first part to show the YTD sales by actual customer numbers, salesman and division if the YTD sales by customers are greater than the YTD budget but I can’t find a way to also show the customers as Others if their YTD sales by are less than YTD budget. Is anyway I can get it done?

Help is appreciated.

Thanks.

[v-xulin-mstf]

Hi @nmtinx1,

Is your issue solved? If the issue has been solved, please adopt the solution to help others.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[nmtinx1]

I am sorry for my late to reply since I was out for a week.  Actually I gave up using PowerBI to create the report; insteads, I use Access to create queries & a marco to get it done after I find out my user only needs the report twice a year. Thank you for your suggestion and follow up. 

Regards,

[v-xulin-mstf]

Hi @nmtinx1,

Could you provide sample data and expected output after removing sensitive data?
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.