
1. Re: How to get count of customers in each category
Deepak Rai Nov 25, 2017 3:55 PM (in response to Rina S)
Sample_Category_Count.twbx 1.1 MB


2. Re: How to get count of customers in each category
Rina S Nov 26, 2017 12:24 PM (in response to Deepak Rai)Hi,
Thanks a lot for looking into this. I added a details page and crosschecked the numbers, butI found a few discrepancies.
For instance, please have a look at Alan Barnes profit category and profit ratio in the attached screen shot.
His profit ratio is 0.42, but is categorized under <0.25.
I think the root cause might be using fixed in Profit Categories calculation as under:
{FIXED [Customer Name]:(IF ([Profit Ratio]) < 0.25 THEN "<0.25"
ELSEIF [Profit Ratio] <0.50 THEN "0.500.25"
ELSEIF [Profit Ratio] < 0.75 THEN "0.750.50"
ELSE ">0.75"
END)}
Highly appreciate any help.
Thanks,
Rina

Sample_Category_Count.twbx 1.2 MB

Dashboard 1.png 98.9 KB


3. Re: How to get count of customers in each category
Shinichiro Murakami Nov 26, 2017 2:20 PM (in response to Rina S)Hi Rina, Deepak,
Sorry for jumping in, but if you add quarter as dimension, Profit Categories should be below?
Thanks,
Shin

4. Re: How to get count of customers in each category
Rina S Nov 26, 2017 4:21 PM (in response to Shinichiro Murakami)Hi,
First of all, thanks a lot for your solution, Shin.
Actually I need just one value per customer for all the selected quarters. So if only 1 quarter is selected, your solution shows what I need, but when multiple quarters are selected, then it double counts some of the customers. I have added a chart to display the total number of distinct customers in the quarters and I need the total count of customers per Profit & Discount categories to match it. But per your solution, when I select 4 quarters, then the total count goes to 948, whereas there all total of 693 customers in the selected quarters.
Thanks,
Rina

5. Re: How to get count of customers in each category
Deepak Rai Nov 26, 2017 4:39 PM (in response to Rina S)Yes, What happens if you use INCLUDE along with Quarter instead of FIXED?

6. Re: How to get count of customers in each category
Rina S Nov 26, 2017 5:19 PM (in response to Deepak Rai) 
7. Re: How to get count of customers in each category
Shinichiro Murakami Nov 26, 2017 5:24 PM (in response to Rina S) 
8. Re: How to get count of customers in each category
Rina S Nov 26, 2017 9:22 PM (in response to Shinichiro Murakami)Hi,
Thanks Shin for your solution. In order to verify the profit ratio, I created an aggregated profit/sales column and tried to match the numbers. But unfortunately, the numbers do not tie up.
Looks like we are missing something in the formula. I tried a lot with various LoD's but unfortunately I am not able to get the right numbers.
Thanks again for your help,
Rina

9. Re: How to get count of customers in each category
Rina S Nov 26, 2017 9:29 PM (in response to Shinichiro Murakami)Hi,
Thanks Deepak for your solution. In order to verify the profit ratio, I created an aggregated profit/sales column and tried to match the numbers. But unfortunately, the numbers do not tie up.
Looks like we are missing something in the formula. I tried a lot with various LoD's but unfortunately I am not able to get the right numbers.
Thanks again for your help,
Rina

10. Re: How to get count of customers in each category
Shinichiro Murakami Nov 26, 2017 10:19 PM (in response to Rina S)H Rina,
I don't not how you aggregated the sales and profit, but as long as I checked, it matches.
without quarter.
with quarter
Regards,,
Shin

11. Re: How to get count of customers in each category
Rina S Nov 26, 2017 11:49 PM (in response to Shinichiro Murakami)Hi Shin,
Thankyou for your reply, Shin.
From the chart having count of numbers in Sheet 1, I am assuming that you are using the measure 'Profit Ratio2' to represent Profit/Sales per customer for any number of quarters. So I compared Profit Ratio2 with calculated column 'Calc Profit Ratio' which is just SUM([Profit])/SUM([Sales]). What I found out was Profit Ratio2 does not tie with Calc Profit Ratio (but Profit ratio ties with Calc Profit ratio). Please see the below image:
In the workbook that you very kindly provided, I saw that in both the sheets Sheet2 & Sheet 5, Profit ratio is used and none of them have Profit ratio2.
So I am little confused if you plan to use Profit ratio or Profit ratio2.
But, just to recap, the problem with Profit ratio2 is that it doesn't tie with Calc Profit ratio while the issue with Profit ratio is that it doesn't seem to work for multiple quarters, as
can be seen in the following image:
Alan Barnes(2nd row) is classified under <0.255, but he has Profit ratio of 0.42.
Please guide in case I have not understood your solution.
Appreciate your help & Thanks a lot!!!
Rina

12. Re: How to get count of customers in each category
Shinichiro Murakami Nov 27, 2017 1:09 PM (in response to Rina S)HI Rina,
sorry, the question comes back to the starting point.
How do you want to show the number on summary table top left.
Because many customers is located at different category in different quarter, the total does not match.
How you want to show each customers on summary table?
If you want to show with selected months total category, completely independent calculation set is required to calculate correct category.
Thanks,
Shin

13. Re: How to get count of customers in each category
Rina S Nov 30, 2017 4:06 PM (in response to Shinichiro Murakami)Hi,
Sorry about the delay in my reply.
For all the selected quarters, it has to be determined which category a customer will fall into. For this to happen, all the calculations i.e. profit, sales and discount has to be added together and then the ratio determined.
I hope it is clear what my question is. Please feel free to ask in case of any other questions.
Thanks,
Rina

14. Re: How to get count of customers in each category
Shinichiro Murakami Nov 30, 2017 4:15 PM (in response to Rina S)HI Rina,
In that sense, double counting of customer are already involved [# of Customers] X [ # of Quarter] will be counted.
If you seek total 659, each customer needs to fall in Specific category ACROSS period.
Shin