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


This is now getting even more confusing.  What INTERSECT does is clear it returns a table of TOP 20 production units. 

 

This is the INTERSECT Table values below. We got 20 rows here

 

 


You do a COUNTROWS over this table above and WHAT HAPPENS next is not clear  

Could you please so kind to be more specific

 

Not really sure what do you mean here... "When Match the PUNIT in TOP 20 is one ROW with the Production Unit." ? 

 

Do you mean if we have a match between TOP 20 Production Unit and a Production Unit in the T_PU Dimension  ? 

 

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 ? 
 

 

Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

Anonymous

 

Use this formula involve in a visual not in a New Table

 

The context made the trick.