cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fabo Frequent Visitor
Frequent Visitor

Bottom 10 using TOPN (no RANKX)

Hello everyone!

 

I have a table called PU (production unit or plant) with two columns: PU[PU ID] (key) and PU[Production Unit] (name of 219 plants, from PU0 to PU218). 

 

I also have a fact table called Production with two columns: Production[PU ID] (key) and Production[Production] (production quantity).

 

Both tables are related through the key (obviously).

 

To create a Top 10 bar char, I used these measures:

 

Production TOP 10 =
  IF(
    COUNTROWS(
      INTERSECT(
        VALUES(PU[Production Unit]);
        TOPN(10,0; ALLSELECTED(PU[Production Unit]); [Sum of Production]; DESC)
      )
    ) > 0,0;
    [Sum of Production];
    BLANK()
  )

 

Sum of Production = SUM(Production[Production])

 

No problem so far.

 

But... I need to create a BOTTOM 10 bar chart.  The only way I got it was changing DESC -> ASC and 10,0 -> 58,0 i.e.

 

Production Bottom 10 = 
  IF(
    COUNTROWS(
      INTERSECT(
        VALUES(PU[Production Unit]);
        TOPN(58,0; ALLSELECTED(PU[Production Unit]); [Sum of Production]; ASC)
      )
    ) > 0,0;
    [Sum of Production];
    BLANK()
  )

 

The problem is that 58,0 is meaningless.  I got it by trying and failing.  It's a temporary patch I am using by now, but I really need to fix it.

 

Any suggestion?

 

Greetings!

 

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: Bottom 10 using TOPN (no RANKX)

@Phil_Seamark - I see you already replied - I happened to be looking at this at the same time Smiley Happy

 

@fabo -
I suspect the underlying issue is that you have something like 48 Production Units which either don't appear in the Production table or have blank Production. So when the Bottom 10 are displayed, their [Sum of Production ] is blank and you end up with a blank visual. But if that's not the case, the below suggestions may not help at all!

 

To exclude Production Units that don't appear in the Production table, you can rewrite the measures to filter PU to only rows for Production Units that appear in the Production table. I would also rewrite the COUNTROWS ( INTERSECT (...) ) to something using KEEPFILTERS. I also ran the TOPN over PU rather than a specific column of PU to make it more general.

 

Version 1 of the measure would exclude PUs that don't appear in Production:

 

Production Bottom 10 =
CALCULATE (
    [Sum of Production];
    KEEPFILTERS (
        CALCULATETABLE (
            CALCULATETABLE ( TOPN ( 10; PU; [Sum of Production]; ASC ); Production );
            ALLSELECTED ( PU )
        )
    )
)

The green filter argument filters PU to only rows corresponding to the Production table.
Top 10 can be written identically using DESC.

 

 

Version 2 of the measure would exclude PUs that either don't appear in Production or appear but have blank Production:

Production Bottom 10 = 
CALCULATE (
    [Sum of Production];
    KEEPFILTERS (
        CALCULATETABLE (
            CALCULATETABLE (
                TOPN ( 10; PU; [Sum of Production]; ASC );
                CALCULATETABLE ( Production; NOT ( ISBLANK ( Production[Production] ) ) )
            );
            ALLSELECTED ( PU )
        )
    )
)

The green filter argument filters PU to only rows corresponding to the Production table with nonblank Production.

Again Top 10 can be written identically using DESC.

 

Well that's an idea anyway - interested in whether it works.

 

Dummy model here that I used to work these out.

 

Regards

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




5 REPLIES 5
Phil_Seamark Super Contributor
Super Contributor

Re: Bottom 10 using TOPN (no RANKX)

Hi @fabo

 

What happens when you try

 

TOPN(10,0; ALLSELECTED(PU[Production Unit]); [Sum of Production]; ASC)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

fabo Frequent Visitor
Frequent Visitor

Re: Bottom 10 using TOPN (no RANKX)

Hi @Phil_Seamark

 

No data is shown.  

TOPN returns just a blank table.

 

Captura.PNG

 

 

 

Highlighted
Phil_Seamark Super Contributor
Super Contributor

Re: Bottom 10 using TOPN (no RANKX)

Hi @fabo

 

I'm a little confused about your top measure.  What is it trying to achieve?  It looks like it's just giving you a sum of ALL your products and not just your ten best product by revenue.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

OwenAuger Super Contributor
Super Contributor

Re: Bottom 10 using TOPN (no RANKX)

@Phil_Seamark - I see you already replied - I happened to be looking at this at the same time Smiley Happy

 

@fabo -
I suspect the underlying issue is that you have something like 48 Production Units which either don't appear in the Production table or have blank Production. So when the Bottom 10 are displayed, their [Sum of Production ] is blank and you end up with a blank visual. But if that's not the case, the below suggestions may not help at all!

 

To exclude Production Units that don't appear in the Production table, you can rewrite the measures to filter PU to only rows for Production Units that appear in the Production table. I would also rewrite the COUNTROWS ( INTERSECT (...) ) to something using KEEPFILTERS. I also ran the TOPN over PU rather than a specific column of PU to make it more general.

 

Version 1 of the measure would exclude PUs that don't appear in Production:

 

Production Bottom 10 =
CALCULATE (
    [Sum of Production];
    KEEPFILTERS (
        CALCULATETABLE (
            CALCULATETABLE ( TOPN ( 10; PU; [Sum of Production]; ASC ); Production );
            ALLSELECTED ( PU )
        )
    )
)

The green filter argument filters PU to only rows corresponding to the Production table.
Top 10 can be written identically using DESC.

 

 

Version 2 of the measure would exclude PUs that either don't appear in Production or appear but have blank Production:

Production Bottom 10 = 
CALCULATE (
    [Sum of Production];
    KEEPFILTERS (
        CALCULATETABLE (
            CALCULATETABLE (
                TOPN ( 10; PU; [Sum of Production]; ASC );
                CALCULATETABLE ( Production; NOT ( ISBLANK ( Production[Production] ) ) )
            );
            ALLSELECTED ( PU )
        )
    )
)

The green filter argument filters PU to only rows corresponding to the Production table with nonblank Production.

Again Top 10 can be written identically using DESC.

 

Well that's an idea anyway - interested in whether it works.

 

Dummy model here that I used to work these out.

 

Regards

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




fabo Frequent Visitor
Frequent Visitor

Re: Bottom 10 using TOPN (no RANKX)

Hi @OwenAuger.

 

You were right about the missing units in the fact table.  Your suggestion works just fine in my data model.  Thank you so much!

 

@Phil_Seamark, thanks to you too for your comments!

 

Best regards,

 

Fabo

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 396 members 3,850 guests
Please welcome our newest community members: