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. V...
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

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

      • fabo's avatar
        fabo
        Advocate III
        9 years ago

        Hi Vvelarde.

         

        It's exactly what I was trying to do.  And it worked greatly.  I just had to add one decimal place to numbers (20,0 and 0,0) to be able to run it.  But that is a regional issue.

         

        Thank you so much for your help!  :smileyvery-happy:

         

        Best Regards!

         

        fabo