Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi There,
I am looking for help to create few measures. My attempts to seek guidance was not fruitful and hence posting here. I have put the datatable below along with expected answer for ease of answering.
Below are the 3 questions (more detailed with expected answer in "What measures are needed" tab in attached excel:
1. How to calculate the number of customers for whom a certain number of services were rendered?
2. How to identify new customers?
3. How to identify Lost customers?
I used this measure but was not able to identify whether it was "Consultancy" or "Implementation" through filters -
YearMonthCountryCustomer NameService RenderedDocument noRevenueCash Collected
2015 | Jan | USA | Customer 1 | Consultancy | 892 | $ 100 | $ 100 |
2015 | Jan | USA | Customer 1 | Implementation | 892 | $ 80 | $ 40 |
2015 | Jan | USA | Customer 1 | Consultancy | 981 | $ 240 | $ 150 |
2015 | Jan | USA | Customer 2 | Consultancy | 989 | $ 110 | $ 100 |
2015 | Jan | USA | Customer 2 | Implementation | 1003 | $ 100 | |
2015 | Jan | USA | Customer 3 | Implementation | 1021 | $ 300 | $ 250 |
2015 | Jan | USA | Customer 3 | Consultancy | 1021 | $ 750 | $ 500 |
2015 | Jan | USA | Customer 4 | Consultancy | 1022 | $ 180 | $ 150 |
2015 | Feb | USA | Customer 1 | Implementation | 1055 | $ 400 | $ 340 |
2015 | Feb | USA | Customer 2 | Implementation | 1057 | $ 200 | $ 200 |
2015 | Feb | USA | Customer 3 | Implementation | 1060 | $ 600 | $ 600 |
2015 | Feb | USA | Customer 5 | Consultancy | 1077 | $ 550 | $ 750 |
2015 | Feb | USA | Customer 6 | Consultancy | 1087 | $ 50 | $ 75 |
2015 | Feb | USA | Customer 7 | Consultancy | 1088 | $ 75 | |
2015 | Jan | India | Customer 1 | Consultancy | 892 | 80 | 80 |
2015 | Jan | India | Customer 1 | Implementation | 892 | 64 | 32 |
2015 | Jan | India | Customer 1 | Consultancy | 981 | 192 | 120 |
2015 | Jan | India | Customer 2 | Consultancy | 989 | 88 | 64 |
2015 | Jan | India | Customer 2 | Implementation | 1003 | 80 | |
2015 | Jan | India | Customer 3 | Implementation | 1021 | 240 | 200 |
2015 | Jan | India | Customer 3 | Consultancy | 1021 | 600 | 400 |
2015 | Jan | India | Customer 4 | Consultancy | 1022 | 144 | 120 |
2015 | Jan | India | Customer 4 | Implementation | 1022 | 320 | 320 |
2015 | Feb | India | Customer 2 | Implementation | 1057 | 160 | |
2015 | Feb | India | Customer 3 | Implementation | 1060 | 480 | |
2015 | Feb | India | Customer 5 | Consultancy | 1077 | 440 | 600 |
2015 | Feb | India | Customer 6 | Consultancy | 1087 | 40 | 60 |
2015 | Feb | India | Customer 7 | Consultancy | 1088 | 60 |
Question: | ||||
Qn 1 | In a selected period & Country, how many number of customers in USA & India were provided: | |||
# 1 | Both types of services (i.e., Consultancy & Implementation) | |||
Expected Ans: | USA in Jan = 2 customer (Customer 1 & 3). Customer 2 had zero revenue on implementation and hence excluded | |||
# 2 | Only one type of service (either Consultancy or Implementation) … sum of 3 & 4 below | |||
Expected Ans: | USA in Feb = 5 customers (Customer 1, 2, 3, 5 & 6). Customer 7 has no revenue and hence excluded | |||
# 3 | Only Consultancy Services | |||
Expected Ans: | USA in Jan = 2 customers (Customer 2 & 4). Customer 2 has zero revenue on Implementation and counted for one type of service count | |||
# 4 | Implementation Services | |||
Expected Ans: | India in Feb = 2 customers (Customer 2 & 3) | |||
All calculations need to exclude customers who had zero Revenue but had cash collections | ||||
Qn 2 | In a selected period & country, how many new* customers were made | |||
Expected Ans: | USA in Feb = 2 customers (Customer 5 & 6). Customer 7 had zero Revenue in Feb and hence excluded | |||
If a customer was not in the database in any prior month, the customer is identified as new | ||||
To clarify, a customer for 'consultany' in a prior month is provided 'implementation' services for the first time in the current month is NOT a new customer | ||||
Qn 3 | In a selected period & country, how many old customers were lost | |||
Expected Ans: | India in Feb = 3 customers(Customer 1, 2 & 4). Customer 2 is considered as lost customer on account of zero revenue in Feb | |||
If a customer ceases to have revenue in any month, the customer is identified as Lost in that month | ||||
Regards
Minosh
Solved! Go to Solution.
Hi,
I'd like to solve this question in steps. You may refer to my solution to your first question in this PBI file. For question 4 (Only Implementation services), why should the answer be 2? - there is no billing for Customers 2 and 3 in India in Feb 2015. In the matrix, i have also shown Customer names for your additional names. You should be able to drag any field in/out of the filter/matrix.
Let me know how this works. If there are no mistakes, we will proceed to the next questions.
Hope this helps.
Hi,
Thank you for the feedback. Could you kinldy help me with data for a few years so i can correct for the error. If i add fictitious data, i may still not be able to catch the error.
Hi,
I'd like to solve this question in steps. You may refer to my solution to your first question in this PBI file. For question 4 (Only Implementation services), why should the answer be 2? - there is no billing for Customers 2 and 3 in India in Feb 2015. In the matrix, i have also shown Customer names for your additional names. You should be able to drag any field in/out of the filter/matrix.
Let me know how this works. If there are no mistakes, we will proceed to the next questions.
Hope this helps.
Let me start by saying Thank you Very much!
Yes, you are right that expected answer to Qn 4 is not 2 as they have zero revenue in the period. I should have put it as USA for Feb should be 2 (Customer 5 & 6).
Let me look through your PBI file and see if that works.
Thanks again
You are welcome. Look forward to your feedback.
Dear Ashish - This works perfectly! Great work! Thank you!
Posting this as Accepted Solution.
Would you mind helping on the new clients and lost clients measure?
Hi,
I made a minor chage to the Measure "Lost Customers". Please download the file again. The link shared earlier should work.
Hi,
For new and lost customers, you may downlod the PBI file from here. Customer 3 is also a lost customer in India in Feb (there was 0 revenue from Customer 3).
Hope this helps.
The new client count DAX formula is not giving desired result. It's calculating the total number of client for a selected period.
Just to add on ... "Date when revenue was first booked" is showing the first date of the period in the data and not the first date of the user selected period. In the sample data that you created, there is only one year and hence not showing the error. Can you please relook at your formula with multi-years.
Hi,
Thank you for the feedback. Could you kinldy help me with data for a few years so i can correct for the error. If i add fictitious data, i may still not be able to catch the error.
Hi Ashish - Please find data for 2 years
YearMonthCountryCustomer NameService RenderedDocument noRevenueCash Collected
2015 | Jan | USA | Customer 1 | Consultancy | 892 | $ 100 | $ 100 |
2015 | Jan | USA | Customer 1 | Implementation | 892 | $ 80 | $ 40 |
2015 | Jan | USA | Customer 1 | Consultancy | 981 | $ 240 | $ 150 |
2015 | Jan | USA | Customer 2 | Consultancy | 989 | $ 110 | $ 100 |
2015 | Jan | USA | Customer 2 | Implementation | 1003 | $ 100 | |
2015 | Jan | USA | Customer 3 | Implementation | 1021 | $ 300 | $ 250 |
2015 | Jan | USA | Customer 3 | Consultancy | 1021 | $ 750 | $ 500 |
2015 | Jan | USA | Customer 4 | Consultancy | 1022 | $ 180 | $ 150 |
2015 | Feb | USA | Customer 1 | Implementation | 1055 | $ 400 | $ 340 |
2015 | Feb | USA | Customer 2 | Implementation | 1057 | $ 200 | $ 200 |
2015 | Feb | USA | Customer 3 | Implementation | 1060 | $ 600 | $ 600 |
2015 | Feb | USA | Customer 5 | Consultancy | 1077 | $ 550 | $ 750 |
2015 | Feb | USA | Customer 6 | Consultancy | 1087 | $ 50 | $ 75 |
2015 | Feb | USA | Customer 7 | Consultancy | 1088 | $ 75 | |
2015 | Jan | India | Customer 1 | Consultancy | 892 | 80 | 80 |
2015 | Jan | India | Customer 1 | Implementation | 892 | 64 | 32 |
2015 | Jan | India | Customer 1 | Consultancy | 981 | 192 | 120 |
2015 | Jan | India | Customer 2 | Consultancy | 989 | 88 | 64 |
2015 | Jan | India | Customer 2 | Implementation | 1003 | 80 | |
2015 | Jan | India | Customer 3 | Implementation | 1021 | 240 | 200 |
2015 | Jan | India | Customer 3 | Consultancy | 1021 | 600 | 400 |
2015 | Jan | India | Customer 4 | Consultancy | 1022 | 144 | 120 |
2015 | Jan | India | Customer 4 | Implementation | 1022 | 320 | 320 |
2015 | Feb | India | Customer 2 | Implementation | 1057 | 160 | |
2015 | Feb | India | Customer 3 | Implementation | 1060 | 480 | |
2015 | Feb | India | Customer 5 | Consultancy | 1077 | 440 | 600 |
2015 | Feb | India | Customer 6 | Consultancy | 1087 | 40 | 60 |
2015 | Feb | India | Customer 7 | Consultancy | 1088 | 60 | |
2014 | Dec | USA | Customer 1 | Consultancy | 742 | 100 | 100 |
2014 | Dec | USA | Customer 1 | Implementation | 742 | 80 | 40 |
2014 | Dec | USA | Customer 1 | Consultancy | 755 | 240 | 150 |
2014 | Dec | USA | Customer 2 | Consultancy | 765 | 110 | 100 |
2014 | Dec | USA | Customer 2 | Implementation | 801 | 100 | |
2014 | Dec | USA | Customer 8 | Implementation | 802 | 300 | 250 |
2014 | Dec | USA | Customer 8 | Consultancy | 802 | 750 | 500 |
2014 | Dec | USA | Customer 9 | Consultancy | 803 | 180 | 150 |
2014 | Dec | India | Customer 1 | Consultancy | 805 | 80 | 80 |
2014 | Dec | India | Customer 1 | Implementation | 806 | 64 | 32 |
2014 | Dec | India | Customer 1 | Consultancy | 809 | 192 | 120 |
2014 | Dec | India | Customer 2 | Consultancy | 911 | 88 | 64 |
2014 | Dec | India | Customer 2 | Implementation | 911 | 80 | |
2014 | Dec | India | Customer 3 | Implementation | 913 | 240 | 200 |
2014 | Dec | India | Customer 3 | Consultancy | 913 | 600 | 400 |
2014 | Dec | India | Customer 10 | Consultancy | 920 | 144 | 120 |
2014 | Dec | India | Customer 11 | Implementation | 921 | 320 | 320 |
2014 | Dec | India | Customer 12 | Consultancy | 925 | 320 | 320 |
Hi Ashish - Were you able to have a look? Please help
Hi,
Thank you for sharing the dataset. Please also let me know in which specific selection scenaio are you getting an incorrect result and what the should be the correct result.
Hi,
Not yet. i will see it on my weekend. Please also let me know in which specific selection scenaio are you getting an incorrect result and what the should be the correct result.
Thanks much Ashish! I have a problem with this one - it is not the problem with the formula what you did but rather something at my end. I have a report level filter that looks only FY19 Q1 for few other things to work and because of that I am getting "Blank" as count of new clients. So essentially, I am looking to see new clients in FY19 Q1. Would you know how best to fix this?
Hi,
I have blogged about my solution to this problem here - Customer analysis by Country and time period.
Hope this helps.
Sorry, i would not know.
You are welcome.
Just to add, when "Services Rendered" is added as a filter and a particular Service is selected, the answers should still be correct
Hi @Anonymous,
Have you tried changing the group by order to DataTable[Service Rendered] and use the count of customer as a measure ?
Cheers
CheenuSing
Thanks but that doesn't work.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |