Need to show the ranking data based on grouping column

[Narukkp]

Hi Team,

I have data like UserName, Category, Total Tickets. Now my requirement is I want to display Group column like each user wise top 3 categories should show their own names, if category column having No category, then it should as No Category only remain all categories should show as “Other”. For this I have created one rank and Group measure, and it is working as expected. But in my report we should not show the category and rank column.  Required output like below.

Rank =

 IF(ISBLANK([Total Tickets]),BLANK(),RANKX(ALLSELECTED(AR_Snow_Tickets[Category]),[Total Tickets],,DESC,Dense))

Group=

IF([Rank]<=3,MIN(AR_Snow_Tickets[Category]),IF([Rank]>3 && MIN(AR_Snow_Tickets[Category]) <> "No Category","Other",MIN(AR_Snow_Tickets[Category])))

Below example with category and rank column:

Requried Out put is 

[v-xinruzhu-msft]

Hi,

Thanks for the solution mh2587 offered and i want to offer some more information for user to refee to.

hello @Narukkp , based on your descrition you can refer to the following solution.

1.Create a new table.

Table2 =
VAR a =
    SUMMARIZE ( 'Table', [UserName], 'Table'[Category] )
VAR b =
    ADDCOLUMNS ( VALUES ( 'Table'[UserName] ), "Category", "Other" )
RETURN
    UNION ( a, b )

2.Then create a measure

MEASURE =
VAR a =
    SUMMARIZE (
        TOPN (
            3,
            FILTER ( ALLSELECTED ( 'Table' ), [UserName] IN VALUES ( 'Table'[UserName] ) ),
            CALCULATE ( SUM ( 'Table'[Total Tickets] ) ), DESC
        ),
        [Category]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table2'[UserName] )
            IN VALUES ( 'Table'[UserName] )
                && OR (
                    SELECTEDVALUE ( 'Table2'[Category] ) IN a,
                    SELECTEDVALUE ( 'Table2'[Category] ) = "No Category"
                ),
        CALCULATE (
            SUM ( 'Table'[Total Tickets] ),
            'Table'[UserName] IN VALUES ( 'Table2'[UserName] ),
            'Table'[Category] IN VALUES ( 'Table2'[Category] )
        ),
        IF (
            SELECTEDVALUE ( 'Table2'[Category] ) = "Other",
            CALCULATE (
                SUM ( 'Table'[Total Tickets] ),
                'Table'[UserName] IN VALUES ( 'Table2'[UserName] ),
                NOT ( 'Table'[Category] IN a )
                    && 'Table'[Category] <> "No Category"
            )
        )
    )

3.Then put the following field to a table visual.

Output

Best Regards!

Yolo Zhu

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

[Narukkp]

@v-xinruzhu-msft 
Thank you for your reply. In my report "other" group is not coming using above code. What i observe is i have other columns in my table like UserName,Category,Created date , closed date, Total Tickets... like having few extra columns in my table but in my visual i required only username,categorygroup,Total Count based on above conditons.
Did you think is extra columns is causing issue ?

[v-xinruzhu-msft]

Hi @Narukkp 

Thanks for your quick reply, if your group is a measure, it cannot return the result you want, so you need to create the group as a calculated column.

Best Regards!

Yolo Zhu

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

[Narukkp]

@v-xinruzhu-msft ,
I am trying to create Group column as Caluculate column instead of Measure but result is not coming as expected. 
Can you please help on this  ? How to create a caluculate column for required group column output 

[Narukkp · Accepted solution]

Hi 
I got the solution. 
I created one Usercategory another table and given relation betwene new table and existed main table. After that i created one measure. Using below scritps i achived 

UserCategory =

DISTINCT(UNION(

                   DISTINCT(AR_Snow_Tickets[Category_Of_Request_Nbr])

                  ,DATATABLE("CategoryNbr",STRING,{{"Other"}})

                  ))

Top3Categorys =
VAR TopCatTable =
TOPN ( 3, ALLSELECTED ( 'UserCategory' ), [Total Tickets] )
VAR TopCatCnt =
CALCULATE ( [Total Tickets], KEEPFILTERS ( TopCatTable ) )
VAR NoCatCnt =
CALCULATE (
[Total Tickets],
KEEPFILTERS ( UserCategory[Category_Of_Request_Nbr] = "No Category" )
)
VAR OtherCnt =
(
CALCULATE (
[Total Tickets],
ALLSELECTED ( 'UserCategory' ),
KEEPFILTERS ( UserCategory[Category_Of_Request_Nbr] <> "No Category" )
)
)
- CALCULATE (
[Total Tickets],
TopCatTable,
KEEPFILTERS ( UserCategory[Category_Of_Request_Nbr] <> "No Category" )
)
VAR CurrentCat =
SELECTEDVALUE ( UserCategory[Category_Of_Request_Nbr] )
RETURN
IF (
CurrentCat = "No Category",
NoCatCnt,
IF ( CurrentCat <> "Other", TopCatCnt, OtherCnt )
)

[mh2587]

Use Table visual with columns UserName,Group, and Total Tickets sort it on Total Tickets in visual option the result will look like required out, if you have already the group column?

[Narukkp]

@mh2587 ,
I do not have group column. That is i created as measure using rank and category column.