Re: Top N dynamic and others

NimaiAhluwalia · ‎07-22-2021

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

Country	Rank	Sales cumulative	Percent
	1
US	1	103,774,983,599.00	40.48%
JAPAN	2	123,583,832,903.00	48.20%
CHINA	3	143,194,182,989.00	55.85%
GERMANY	4	155,827,591,368.00	60.78%
FRANCE	5	164,516,421,698.00	64.17%
ITALY	6	172,651,060,426.00	67.34%
UK	7	179,731,952,617.00	70.10%
SPAIN	8	186,576,126,423.00	72.77%
BRAZIL	9	192,969,699,785.00	75.26%
CANADA	10	198,763,452,008.00	77.52%
INDIA	11	203,163,139,833.00	79.24%
RUSSIA	12	207,217,106,984.00	80.82%
KOREA	13	211,219,848,002.00	82.38%
AUSTRALIA	14	213,797,651,898.00	83.39%
MEXICO	15	216,031,838,455.00	84.26%
POLAND	16	218,175,441,462.00	85.10%
ARGENTINA	17	219,940,592,809.00	85.78%
TURKEY	18	221,702,844,090.00	86.47%
SAUDI ARABIA	19	223,405,987,034.00	87.14%
SWITZERLAND	20	225,102,062,850.00	87.80%
TAIWAN	21	226,763,849,208.00	88.45%
BELGIUM	22	228,411,087,629.00	89.09%
AUSTRIA	23	229,727,082,419.00	89.60%
SWEDEN	24	230,968,435,855.00	90.09%
THAILAND	25	232,143,312,908.00	90.54%
PORTUGAL	26	233,223,534,967.00	90.97%
NETHERLANDS	27	234,291,211,463.00	91.38%
PHILIPPINES	28	235,308,819,030.00	91.78%
GREECE	29	236,287,814,392.00	92.16%
ROMANIA	30	237,231,922,631.00	92.53%
EGYPT	31	238,147,091,877.00	92.89%
SOUTH AFRICA	32	239,054,048,331.00	93.24%
PUERTO RICO	33	239,896,394,781.00	93.57%
CZECH REPUBLIC	34	240,729,233,342.00	93.89%
INDONESIA	35	241,528,295,197.00	94.20%
VIETNAM	36	242,312,813,668.00	94.51%
FINLAND	37	243,086,251,381.00	94.81%
BANGLADESH	38	243,833,524,314.00	95.10%
ALGERIA	39	244,558,262,060.00	95.39%
DENMARK	40	245,271,848,147.00	95.66%
IRELAND	41	245,960,569,503.00	95.93%
PAKISTAN	42	246,638,479,919.00	96.20%
NORWAY	43	247,314,069,567.00	96.46%
HUNGARY	44	247,968,910,926.00	96.72%
CENTRAL AMERICA	45	248,609,165,531.00	96.97%
BULGARIA	46	249,129,928,422.00	97.17%
CHILE	47	249,622,441,793.00	97.36%
ECUADOR	48	250,039,376,094.00	97.52%
HONG KONG	49	250,451,293,490.00	97.68%
UAE	50	250,856,626,985.00	97.84%
COLOMBIA	51	251,260,699,514.00	98.00%
SLOVAKIA	52	251,640,973,404.00	98.15%
FRENCH WEST AFRICA	53	252,015,852,215.00	98.29%
MOROCCO	54	252,389,801,328.00	98.44%
MALAYSIA	55	252,734,017,630.00	98.57%
CROATIA	56	253,029,112,205.00	98.69%
SERBIA	57	253,317,383,052.00	98.80%
KAZAKHSTAN	58	253,591,424,387.00	98.91%
NEW ZEALAND	59	253,857,532,893.00	99.01%
PERU	60	254,104,207,364.00	99.11%
DOMINICAN REPUBLIC	61	254,348,006,382.00	99.20%
LEBANON	62	254,575,679,191.00	99.29%
BELARUS	63	254,795,756,910.00	99.38%
SLOVENIA	64	255,010,097,513.00	99.46%
TUNISIA	65	255,219,148,693.00	99.54%
SINGAPORE	66	255,414,945,634.00	99.62%
LITHUANIA	67	255,608,407,650.00	99.70%
URUGUAY	68	255,715,968,443.00	99.74%
BOSNIA	69	255,819,697,333.00	99.78%
VENEZUELA	70	255,911,140,291.00	99.81%
LATVIA	71	256,001,582,608.00	99.85%
JORDAN	72	256,089,541,889.00	99.88%
ESTONIA	73	256,170,546,289.00	99.92%
KUWAIT	74	256,250,105,125.00	99.95%
SRI LANKA	75	256,324,199,932.00	99.98%
LUXEMBOURG	76	256,388,112,703.00	100.00%

Desired Output - As soon as the Percent reaches 80 the below countries should be clubbed in the others with all their sales

Country	Rank	Sales cumulative	Percent
	1
US	1	103,774,983,599.00	40.48%
JAPAN	2	123,583,832,903.00	48.20%
CHINA	3	143,194,182,989.00	55.85%
GERMANY	4	155,827,591,368.00	60.78%
FRANCE	5	164,516,421,698.00	64.17%
ITALY	6	172,651,060,426.00	67.34%
UK	7	179,731,952,617.00	70.10%
SPAIN	8	186,576,126,423.00	72.77%
BRAZIL	9	192,969,699,785.00	75.26%
CANADA	10	198,763,452,008.00	77.52%
INDIA	11	203,163,139,833.00	79.24%
RUSSIA	12	207,217,106,984.00	80.82%
Other	13	Sum of all the other remaining countries	100.00%

@amitchandak

@parry2k
@Sean
Can you help me in this!

v-yalanwu-msft · ‎07-26-2021

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.

How to get your questions answered quickly -- How to provide sample data

NimaiAhluwalia · ‎07-27-2021

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.

tex628 · ‎07-26-2021

Hi @NimaiAhluwalia ,

First of all you will need to add an "Other" to your Country table. You should be able to do this with a simple append.

Next up regarding the calculation, try this:

Measure = 
SUMX( 
SUMMARIZE ( 'Country' , 
'Country' [Name] , 
"Value" , 
IF( 'Country'[Name] = "Other" , 
SUMX(
SUMMARIZE ( 'Country' , 
'Country' [Name] , 
"Value2" , IF( [Percent] >= 0,81 , [Sales comulative]
), [Value2] ) ,
IF( [Percent] < 0,81 , [Sales comulative] , BLANK() 
) , 
[Value]
)

Br,
J

Connect on LinkedIn

NimaiAhluwalia · ‎07-27-2021

@tex628

Hope you are doing good can you help me with the below error

Regards

tex628 · ‎07-29-2021

Hi @NimaiAhluwalia ,

Sorry for the delay, I'm on vacation ☀️

I made a few small misstakes so try this:

Measure = 

SUMX( 
SUMMARIZE ( 'Country' , 
'Country' [Name] , 
"Value" , 
IF( 'Country'[Name] = "Other" , 
SUMX(
SUMMARIZE ( 'Country' , 
'Country' [Name] , 
"Value2" , IF( [Percent] >= 0,81 , [Sales comulative] )
), 
[Value2] 
) ,

IF( [Percent] < 0,81 , [Sales comulative] )
))
[Value]
)

Otherwise send the file and i can take a look directly!

Br,
J

Connect on LinkedIn

NimaiAhluwalia · ‎07-26-2021

Hello @tex628

I am getting the below error for the following solution

v-yalanwu-msft · ‎07-25-2021

Hi, @NimaiAhluwalia ;

You could create a new table as follows:

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

The final output is shown below:

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.

How to get your questions answered quickly -- How to provide sample data

NimaiAhluwalia · ‎07-26-2021

Hello @v-yalanwu-msft

Thanks for your help the sample data which I had sent in the above post was extracted from a measure for the cumulative sales but I am not able to create the same in the calculated column and my whole solution is stuck due to that I am attaching a pbix file in the private chat have a look and let me know my mistake.

Regards

selimovd · ‎07-22-2021

Hey @NimaiAhluwalia ,

add a row with the country "Others" to your data source table.

Then the following measure should will give you the desired result:

Below 80 and Others = 
VAR vPercent = IF(MAX('DataTable'[Percent]) < 0.8, MAX('DataTable'[Percent]))
VAR vTableAbove80 =
    FILTER(
        ALL( 'DataTable' ),
        'DataTable'[Percent] >= 0.8
    )
RETURN
    IF(
        MAX( 'DataTable'[Country] ) = "Others",
        SUMX(
            vTableAbove80,
            'DataTable'[Percent]
        ),
        vPercent
    )

The same you can then do for the Sales, just change the column in the last SUMX from Percent to the Sales Column.

The result looks then like that:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic