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

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Victor, 


      It's not very obvious why  you are INTERSECTing Unit Values with TOP 20 units by production. The reason is that TOPN is a table funciton as welll as INTERSECT().   Technically, running this without INTERSECT() should yeild the same result but it DOES not. The code below will return the whole list not the TOP 20 (No INTERSECT() in the code below).

       Could you please be so kind to explain how your code is working, especially the INTESCEPT  of VALUES over TOPN part? 

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

       

       

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        9 years ago

        Anonymous

         

        Hi, the dax code work very simple:

         

        1. When a Region is selected show the productions unit to belong to The Regions Selected.

         

        2. When None Region is selected. (This could be read like "All" the regions is selected)

         

        Take The value of the Production Unit (Values) (One by One)

         

        And another Table with The Top 20 of All Selected PUnits. (of All the Regions selected)

         

        I Use The Intersect to evaluate if The Production Unit is included in the Top 20. When the Rows in the Intersect (Table) is greater than 0 means that this PUnit is in the Top 20 so calculate the Sum of Production. If is 0 then Blank(To don't show in the visual)

         

        For every Production Unit repeat this evaluation.

         

         

        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 ()
    )
)

         

        I hope this will be guide to understand the code.

         

        Regards

         

        Victor

        Lima - Peru