Dynamic filter and dynamic column measure

[Harrisfil]

hello ,

I have the following issue. I have a small table:

TopN	MeasureA	Date
Greece	10	1/5/2024
Spain	15	6/8/2024
Singapore	22	15/7/2024
Jamaica	47	1/4/20243

what i  need is to create a parameter filter where i will filter this table per Top2 or Top3

and i need this table to be changed as follows: 

if i select top 2 then i need to see the top 2 countries perYtd and an extra row named " RestofCountries" with the rest total of other unfiltered countries. Any idea? thank you

TopN	MeasureA	Date
Singapore	22	15/7/2024
Jamaica	47	1/4/20243
RestOfcountries	25

[Anonymous]

Hi @Harrisfil ,

I created simple data:

Please follow the steps:

1.In Power Query, click "Reference" and remove columns:

2.Add a step and close Power Query Editor:

= Table.Distinct( Table.InsertRows( #"Removed Columns",1,{[TopN="RestOfcountries"]}))

3.Create a replationship:

4.Create a measure:

Measure2 = 
VAR _table = TOPN(SELECTEDVALUE(Parameter[Parameter]),SUMMARIZE(ALL('Table'),'Table'[TopN],'Table'[Date],"Sales",[Measure]),[Sales])
VAR _top_country = SELECTCOLUMNS(_table,'Table'[TopN])
VAR _other_country = EXCEPT(ALL('Table'[TopN]),_top_country)
VAR _other_country_sales = SUMX(FILTER(ALL('Table'),'Table'[TopN] in _other_country),'Table'[Sales])
RETURN SWITCH(TRUE(),
    SELECTEDVALUE('Table (2)'[TopN]) IN _top_country,MAXX(FILTER(_table,'Table'[TopN]=SELECTEDVALUE('Table (2)'[TopN])),[Sales]),
    SELECTEDVALUE('Table (2)'[TopN]) = "RestOfcountries",_other_country_sales

)

5.The result is as follows:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Harrisfil]

Thank you very much for your time on that! Indeed its working great 🙂 Can i somehow add a date filter as well? so i can have both parameter and date filter in my model.

thank you again

[Anonymous · Accepted solution]

Hi @Harrisfil ,

You can create a new slicer, use the date in the Table as the slicer field, and then modify the formula and use ALLSELECTED function:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Harrisfil]

great thank you very much ! its working great.

something last : can i always  set Rest of countries row in the end of the matrxi table ? i need first to sort it by the measure2 but i need this specific one to be sorted in the end indepedently of the measure asc/desc sorting in the table