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

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