Top N values based on the total count in matrix

[nish18_1990]

Hi ,

I need a top N parameter based on the total count of Sales .  If i select top 2 then it should show me top 2 subcategory based on the total Sales which is displayed on the right side .

Data:

Category	Sub Category	Sales	Quantity Q2	country
Furniture	Chairs	100	10	China
Furniture	Tables	100	20	China
Automobile	Cars	100	30	China
Automobile	Bikes	700	40	China
Furniture	Chairs	100	13	India
Furniture	Tables	500	22	India
Automobile	Cars	100	54	India
Automobile	Bikes	100	60	India
Furniture	Chairs	100	30	China
Furniture	Tables	300	29	China
Automobile	Cars	200	88	China
Automobile	Bikes	100	22	China
Furniture	Chairs	500	21	India
Furniture	Tables	100	33	India
Automobile	Cars	600	54	India
Automobile	Bikes	100	60	India
Furniture	Chairs	100	30	China
Furniture	Tables	100	29	China
Furniture	Sofa	100	88	China
Furniture	Bed	100	22	China
Furniture	Dining	100	21	India
Furniture	Tables	100	33	India
Furniture	Tables	100	54	India
Furniture	Sofa	100	60	India
Automobile	Cars	1000	54	India
Automobile	Bikes	100	60	India
Furniture	Chairs	100	30	China
Furniture	Tables	200	29	China
Furniture	Sofa	100	88	China
Furniture	Bed	100	22	China
Furniture	Dining	100	21	India
Furniture	Tables	400	33	India
Furniture	Tables	800	54	India
Furniture	Sofa	100	60	India

[maruthisp]

Hi @nish18_1990 ,
I tried to implement the solution for the problem. Please check the pbix file.
Top N values based on the total count in matrix.pbix
I checked in a table visual only. Please let me know if there is any questions..

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[m4ni]

Hi @nish18_1990 

If I understand correctly you should be able to acheive this from the filter pane.

Select TOP N from on the Sub category and then enter 2.

I would create a measure for the count of sales and then select that measure as the By Value in the filter pane.

Please see screenshot unsing your data.

Hope this is what you mean.  Otherwise please explain further.

Thanks

[nish18_1990]

No I dont want it in filter tab . I need a dynamic parameter for top N . If somebody choose 5 on parameter it should be top 5 categories . I did achieved it partially by rank .

Var Ranks:

CALCULATE(

    RANKX(

    ALL('My_Main_Table'),

    CALCULATE(

    [#sales],

        ALLEXCEPT('My_Main_Table',My_Main_Table[Subcategory])),,

    DESC,

   Dense

        )

    )

But the issue  i am getting  is same rank for same count , because of that if i select 10 on paramater , I am getting more than 10 sub categories :

[mark_endicott]

@nish18_1990 - You need to find a way to break your ties, beacuse your rank is only happening over one column where some of the values are the same.

Below I have done this with the Quantity and Sub Category columns. 

RANK (
    DENSE,
    ALLSELECTED ( 'Table' ),
    ORDERBY (
        'Table'[Sales], DESC,
        'Table'[Quantity Q2], ASC,
        'Table'[Sub Category], ASC
    ),
    LAST
)

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[mark_endicott]

@nish18_1990 - I thought I would finish off your problem. Given you have not indicated you have found the solution. 

Please find the attached file, which only shows the number of categories within the "top" number selected. 

I have done this by ranking the sales first, and to break the ties I have used Quantity then Sub-category. 

I hope this enables you to see how this can be done. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[nish18_1990]

Hi , 

Its working in table structure but when i convert it in matrix table it is not working . If the table is below form how to use the parameter on basis of the total count of quantity 

[mark_endicott]

@nish18_1990 - Ok this DAX fixes that:

VAR CurrentSubCategory = SELECTEDVALUE('Table'[Sub Category])

VAR QuantityQ2 =
    CALCULATE(
        COUNT('Table'[Quantity Q2]),
        ALLSELECTED('Table'[Sub Category])
    )

VAR SalesTotal =
    CALCULATE(
        SUM('Table'[Sales]),
        ALLSELECTED('Table'[Sub Category])
    )

VAR _rank =
    RANKX(
        ALLSELECTED('Table'[Sub Category]),
        CALCULATE(COUNT('Table'[Quantity Q2])) * 1000000 + (1000000 - CALCULATE(SUM('Table'[Sales]))),
        ,
        ASC,
        Dense
    )

RETURN
    IF ( _rank <= [Parameter Value] , _rank)

But as you will see from the screenshot, there is now a tie for the first rank becuase the count of quantity and sum of sales are the same, how should we resolve this tie?

Also, you should know that using this layout will stop the parameter logic working because you will always have data to display in the columns. 

Using a table is the way to get your parameter logic to work as desired. 

[nish18_1990]

Is there any way we can have different ranks for same numbers ? like first two 1 should be 1 and 2 rank . Is it achievable in POWER BI . Could you please share the power Bi as well. Thanks !!!

[mark_endicott]

@nish18_1990 - it is only acheivable if you tell me how.

For example we could do it on the sub category name, e.g. B is before D so bikes gets 1 and Dining gets 2. 

But I don't know if this is what you are trying to acheive. 

Also, even if we do this, it still will not resolve the issue of the fact that the matrix with both columns and rows will prevent the parameter logic from working. 

[nish18_1990]

Yes need it by subcategory . Attached screenshot below for eg . On the left side the subcategories are different before count . if you see below 11 is coming twice and rank is same . When i do a top 5 in parameter selection on the Var Rank field ,i am getting more than 5 rows because i have same values. Is there any way to just show top 5 rows only even if the count has same value or ranks . Can we do indexing on rows and restrict in intex is <=5 . 

[mark_endicott · Accepted solution]

@nish18_1990 - then you have NO OPTION other than to use a Table visual (not a Matrix). It cannot be a Matrix because a Matrix does not supply the Row context for Sub Category that is needed for the rank. 

If you want to break ties by Sub Category YOU NEED TO USE A TABLE. 

I have attached a final version of this file. Page 1 has the measure Rank 2 which uses the DAX below to rank in the following order:

1. Count of Quantity

2. Sum of sales (to break any ties above)

3. Sub Category A-Z (to further break ties from above)

4. Country A-Z (to break the final ties)

VAR _rank =
    RANK (
        DENSE,
        ALLSELECTED ( 'Table'[Sub Category], 'Table'[country] ),
        ORDERBY (
            CALCULATE ( COUNT ( 'Table'[Quantity Q2] ) ), DESC,
            CALCULATE ( SUM ( 'Table'[Sales] ) ), DESC,
            'Table'[Sub Category], ASC,
            'Table'[country], ASC
        ),
        LAST
    )
RETURN
    IF ( _rank <= [Parameter Value], _rank )

The table also contains two measures for Count of QTY and Sum of Sales that contain logic to return blank when the rank returns blank. This happens when the parameter value is set to show only a certain number of results. - Which has been your requirement all along, and can only be achieved using a TABLE. 

I've now provided this solution multiple times and fine tuned it as much as I can, I'd appreciate it if you could mark it as the solution. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[nish18_1990]

what i need is :

Count      Var rank

38                1

32                2

11                3

11                4

9                  5

5                  6

5                   7

5                  8