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

Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

Anonymous

 

"

For example let's take PU82 (it is in TOP 20) and PU150 this one is not a TOP20 Production Unit. How does the story evolves from here ? "

 

In the Visual you add all the Production Units in Rows. (Axis)

In values is the measure who evaluates for every Production Unit (one to one) if is in the TOP 20 or not.

 

 

 

 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

 

Victor,

 

I did not see the video at first.  Thank you  very much for your detailed explanation. 

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    Anonymous

     

    The logic is this:

     

    The Production Unit PU82 is in the TOP 20 Production Units?  

     

    Yes. INTERSECT bring you a Table with the Row (PU82)

     

    So i use CountRows of INTERSECT Table and the answer is 1 . So the result of the measure is the SUM of Production.

     

    The Production Unit PU10000 is in the TOP 20 Production Units?  

     

    No. INTERSECT bring you a Empty Table. The CountRows of INTERSECT Table and the answer is 0 . So the result of the measure is Blank().