Display Top N values of a table with calculation next to it
-
Hi,
In Power BI Report Builder I need to use SSRS code for my expressions. I am quite new to this and am a little bit stuck on how to retrieve the correct data. To make it easy I created some dummydata.
What I want to retrieve is the top 3 values with a column next to it that shows the percentage of total. For example, "Financial" occurs 9 times out of 25. This would be the top value with 9/25*100% = 36%
Hope someone could help me out there. Thanks in advance!
Kind Regards,
Soof1234
-
The data you posed has nothing to compare.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi,
Isn't this data sufficient as everything to create my table is available are my first thoughts. What do I need according to you to retrieve this then?
Kind regards,
Soof1234
-
You're right. I misread the question.
Since there's no readily consumable data that we can copy and paste from (see the article at the first link in my signature line below for more on that), just do a SELECT with a COUNT(*) and a GROUP BY on the "Problem" column (can't read the full name there) with an ORDER BY on the derived column created by the COUNT(*) and then the TOP 3 from that.
The question becomes... what do you want to do in the case of ties? You may have to get Rank or Dense_Rank involved there.
I try to never post code unless I've tested it with the given data. I don't have the time to convert graphics to consumable data. If you want a coded answer then, like I said, read'n'heed that article at the first link in my signature line below.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Is this a DAX question? DAX answers are super weird, because the language is strange (from a SQL perspective). But super easy in DAX.
=DIVIDE (
COUNTROWS('Table1'),
CALCULATE(
COUNTROWS('Table1'),
ALL('table1')
)
)
haven't done this in SSRS in like a thousand years. I think you have to create a variable to get the count of all rows, stuff it in a variable, and then divide by that.
-
Lordy... my apologies. Really bad day or two, apparently. I totally missed the fact that this was for SSRS 2016. I don't even know how to spell SSRS.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply