[How to write measure] with TOP N + COUNTROWS + VALUES + FILTER functions

DAX DAX Calculations
1

Hello everyone,

I’m new on the forum and got a licence on Enterprise DNA to learn and develop skills in Power BI. I’ve read the rules and try to follow them as much as possible for my 1st topic. I can’t upload the Power BI file here since it’s quite confidential but if really necessary I’ll try to find a way.

Here’s the problem I’m struggling with, I hope it will be clear :

I have a table including, for each row, a reference of article with at least 2 columns in the table : Business Unit (cBu), Quantity (iQte) . Here is what I want to do :

  • Filter the table only on the values < 0 in the column Quantity (iQte)
  • Calculate the total number of articles (rows) for each Business Unit (cBu) category
  • Return the Top 5 highest total of articles per each Business Unit (cBu) category
  • Have a visual for this measure (either a table or graph)

I’ve tried to look up on various sources to come up with a solution, but it doesn’t work yet (I assume I need to user FILTER , COUNTROWS, VALUES and TOPN functions at least).

Thank you already in advance for your support, I guess it may be easy for you but I’m still beginning with the logic of calculations.

Have a nice day ! :slight_smile:
EDNA forum_test.pbix (6.3 MB)

2

@Jeof3 Writing that code is easy but share a sample file, as in past there has been a lot of too and fro and we can eliminate the hassle with PBIX.

3

Hi, thank you already for your reply.

I’ve uploaded a sample file PBIX where I’ve removed all the sensitive & confidential data and non useful tables.
EDNA forum_test.pbix (6.3 MB)

The table here is Stkdet2 (with column cBu and iQte) where I want to do the operation. Could you please help me writing the code ?

Thanks!

4

You dataset doesn’t have 0 in that column.

image
image377×638 10.5 KB

5

@AntrikshSharma : EDIT…
indeed, I’ve actually made a mistake, there are no values = 0 in field iQte .
But if you filter on values <0 on iQte, what would be the measure?
I’ve adapted in above so there’s no confusion…
Thanks !

6

@Jeof3 Is this what you want?
image

EDNA forum_test.pbix (6.3 MB)

1 Like
7

Yes !! Thank you very much for this. I understand better the logic now.

I’ll try to create a ‘Other’ category summing up all the other rows not included in the top 5.

1 Like