Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Narukkp
Helper V
Helper V

Need to show the ranking data based on grouping column

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:

Narukkp_1-1726050863515.png

 

Requried Out put is 

Narukkp_2-1726051081858.png

 

1 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 )
)



View solution in original post

7 REPLIES 7
v-xinruzhu-msft
Community Support
Community Support

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.

vxinruzhumsft_0-1726108096260.png

 

Output

vxinruzhumsft_1-1726108105185.png

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.

 

@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 ?



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.

@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 

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
Super User
Super User

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?


Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.