Categorise sales volumes and number of products that fall into those categories

[Anonymous]

Hi everyone,

I am trying to solve the following:

I have a DimProduct table and a FctSales tabe.

I want to categorise sales so that I can then count the number of products that fits into each category of sales:

Example of the desired output:

SalesCategory	Number of procucts	% of total
1	23,169	46.67%
2	3,508	7.07%
3	20,255	40.8%
4	1,403	2.83%
5	229	0.46%
6-9	990	1.99%
10+	85	0.17%
Total	49,639

I have attempted to solve this by doing the following:

SalesCategory = 
VAR NoPreviousSales = CALCULATE(COUNT('FctSales'[SalesID]),'FctSales'[PreviousSalesID]=0)
return
VAR category = IF (
    NoPreviousSales = 1,
    "1",
    IF (
        NoPreviousSales = 2,
        "2",
        IF (
            NoPreviousSales = 3,
            "3",
            IF (
                NoPreviousSales = 4,
                "4",
                IF (
                    NoPreviousSales = 5,
                    "5",
                    IF (
                        NoPreviousSales > 5
                            && NoPreviousSales < 10,
                        "6-9",
                        IF ( NoPreviousSales > 9, "10+", "0" )
                    )
                )
            )
        )
    )
)
return SalesCategory

If I add this to a table then I get  the table which is fine

SalesCategory
1
2
3
4
5
6-9
10+

When I go and add the count('DimProduct'[ProductID]) to the table visual 

I dont get the expected values as in my above expected output instead I get the following:

SalesCategory	Count of ProductID
6-9	49,639
5	49,639
3	49,639
2	49,639
1	49,639
0	49,639

I have no idea why this is happening... 

[v-eqin-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved by using @johncolley 's method? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,
Eyelyn Qin

[johncolley · Accepted solution]

Does changing the Cross filter direction to Both fix this issue? If not, please provide some sample data. 

[Anonymous]

Hey @johncolley I have just applied that setting and it works! Thank you, BTW do you have any suggestions as to how I can achieve the percentages column? 

[johncolley]

Hi @Anonymous ,

The following measure works on my limited example:

Sales % = 

Var categorycount = COUNT(Sales[ProductID])

Var overallcount = CALCULATE(COUNT(Sales[ProductID]), REMOVEFILTERS(Sales))

Var result = categorycount/overallcount

return

result

I've formatted Sales% as a % and changed decimal to 2 in my example below.

[johncolley]

Hi @Anonymous,

You see that repition of the total number when there is no relationship or a relationship not filtering in the required direction between your two tables. Please review the relationship settings - or share details of it if you require further assistance.

[Anonymous]

Hi @johncolley 

Between my FctSales table I have a relationship on ProductKey that is Many-to-one on my DimProduct table. Cross filter direction: Single