Forum Discussion

NimaiAhluwalia's avatar
NimaiAhluwalia
Icon for Continued Contributor rankContinued Contributor
5 years ago

Top N dynamic and others

Hi, I need to create where I can show all countries whose percent reaches 80% which is RUSSIA in the below case and the rest of the countries below it should be shown in others.   Data   ...
Need Help
v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, NimaiAhluwalia ;

Please try new table:

Newtable = 
VAR _min =
    MINX ( FILTER ( 'EU', [Percent] > 0.8 ), [Percent] )
VAR _table=
   SUMMARIZE(FILTER('EU',[Percent]<=_min),[Country],"rank",[Rank],"rank cumulatie",[rank cumulatie],"percent",[Percent])
VAR _other =
    SUMMARIZE (
        'EU',
        "Country", "Other",
        "Rank",MINX ( FILTER ( 'EU', [Percent] > 0.8 ), [Rank] ) + 1,
        "Sales cumulative", SUMX ( FILTER ( 'EU', [Percent] > _min ), [rank cumulatie] ),
        "Percent", 1)
RETURN  UNION ( _table, _other )

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

NimaiAhluwalia's avatar
NimaiAhluwalia
Icon for Continued Contributor rankContinued Contributor
5 years ago

I tried this solution that gives me a static output in a table I need it dynamic as I need to filter those countries depending upon the product table so instead of creating a table we can create a measure which is dynamic in terms of products 

 

As in when I select any product I need to know which countries comes in the 80% and rest should show in the others.