Forum Discussion

fabo's avatar
fabo
Advocate III
9 years ago
Solved

Conditional Top 20

Hi everyone. 

 

I have this scenario: 219 plants ("Production Unit" or "PU") are located in 15 geographical regions. They produce only one product in different amounts. Data is collected monthly. Visually:

 

 

 The data model is the following:

 

 

What I need to do is a "PU" Top 20 (Plant Top 20) in chart 2 (chart in the right side) only if there is no Region selected in chart 1 (chart in the left side).  If there is at least one Region selected, then chart 2 is simply filtered by that Region showing only the plants located there.

 

 

What I did

 

I added a calculated column in plant table (T_PU) named Rank:

 

Rank =

RANKX(

 ALL(T_PU);

 CALCULATE([Sum of Production])

)

 

And these measures:

 

Sum of Production = SUM(FT_Production[Production])

 

Production by PU =

IF(

 [No of Selected Regions] <> [No of Total Regions];

//This is True when at least one region is selected

 CALCULATE([Sum of Production]);  CALCULATE([Production Top 20])

)

 

No of Selected Regions = DISTINCTCOUNT(T_Region[id_region])

 

No of Total Regions =

CALCULATE(  DISTINCTCOUNT(T_Region[id_region]);  ALL(T_Region)

)

 

Production Top 20 =

CALCULATE(

 [Sum of Production];

 T_PU[Rank] <= 20

)

 

What I got...

 

Two issues:

 

1. A quasi-Top 20 chart. 

 

 

The Top 20 chart seems to be ok but.. The order is not quite right:

 

 

2. When a Region is selected the resulting chart only shows some plants (in some regions, it shows no plant at all).

 

 

Maybe some of my steps are not appropriate. Or maybe I need a complete different approach. Any suggestion will be welcome.

 

 

Regards!

 

Fabo

 

Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    fabo

     

    Hi, Please try with this measure:

     

    ProductionShow =
IF (
    HASONEVALUE ( T_Region[Region] ),
    [Sum of Production],
    IF (
        COUNTROWS (
            INTERSECT (
                VALUES ( T_PU[Production Unit] ),
                TOPN ( 20, ALLSELECTED ( T_PU[Production Unit] ), [Sum of Production], DESC )
            )
        )
            > 0,
        [Sum of Production],
        BLANK ()
    )
)

     

    Regards

     

    Victor

    Lima - Peru

18 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi fabo,

    In your solution, the right chart is weird, please examined carefully? Do you mind share your sample data for further analysis?

    For your right chart, you only find the rows where the rank less than 20, not all the rows. So when you click a region in left chart, it shows the products(were rank <=20) located in the region.

    Best Regards,
    Angelia

    • fabo's avatar
      fabo
      Advocate III
      9 years ago

      Thank you for replying, Angelia! :smileyhappy:

       

      Here is a shared folder with my sample data in OneDrive.  I hope you can find something useful from it.

       

      I got your explanation about my right chart.  I will rethink how I can make it work properly, i.e. showing top 20 only when no region is selected and just filtering otherwise.

       

      fabo

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        9 years ago

        fabo

         

        Hi, Please try with this measure:

         

        ProductionShow =
IF (
    HASONEVALUE ( T_Region[Region] ),
    [Sum of Production],
    IF (
        COUNTROWS (
            INTERSECT (
                VALUES ( T_PU[Production Unit] ),
                TOPN ( 20, ALLSELECTED ( T_PU[Production Unit] ), [Sum of Production], DESC )
            )
        )
            > 0,
        [Sum of Production],
        BLANK ()
    )
)

         

        Regards

         

        Victor

        Lima - Peru