Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
Below example with category and rank column:
Requried Out put is
Solved! Go to 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
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 )
)
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.
@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
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 )
)
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!
@mh2587 ,
I do not have group column. That is i created as measure using rank and category column.
User | Count |
---|---|
90 | |
88 | |
88 | |
79 | |
49 |
User | Count |
---|---|
153 | |
145 | |
106 | |
74 | |
55 |