Bottom 10 using TOPN (no RANKX)

[fabo]

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!

[OwenAuger · Accepted solution]

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

@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

[fabo]

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

[Phil_Seamark]

Hi @fabo

What happens when you try

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

[fabo]

Hi @Phil_Seamark

No data is shown.  

TOPN returns just a blank table.

[Phil_Seamark]

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.