Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

filtering a table by using a mesure (TOPN and SUMX)

Hello everyone.

I al using a mesure below to get a table with just negatives values and then i get a sum of these values ( the number of values is dynamic as i use a what if parametre). However in my table u see that i get a good total but i want to know how to filter my table so if i choose 3 in the parametre i could get only three values in my table and not ALL values.  
I created a rank column in my BASE table ( 

Rank = RANKX(BASE;BASE[ MARGE ACT];BASE[ MARGE ACT];ASC;Dense)
 

So i can reference this column in my measure 
 

SUMX(TOPN('Paramètre'[Valeur Paramètre];FILTER(BASE;BASE[ MARGE ACT]<=0);BASE[Rank];ASC);BASE[ MARGE ACT])
 

But it still doesnt workscr.png

 

 

 


Thanks a lot

 

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

try this code:

 

Measure =
SUMX (
    FILTER (
        BASE;
        BASE[ MARGE ACT] <= 0
            && BASE[Rank] <= SELECTEDVALUE ( 'Paramètre'[Valeur Paramètre] )
    );
    BASE[ MARGE ACT]
)

this measure will only show values for whatever is in the top, but you will still see all rows because e.g. 'Paramètre'  has value for all entries, not just the Top entries

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

1 REPLY 1
Stachu
Community Champion
Community Champion

try this code:

 

Measure =
SUMX (
    FILTER (
        BASE;
        BASE[ MARGE ACT] <= 0
            && BASE[Rank] <= SELECTEDVALUE ( 'Paramètre'[Valeur Paramètre] )
    );
    BASE[ MARGE ACT]
)

this measure will only show values for whatever is in the top, but you will still see all rows because e.g. 'Paramètre'  has value for all entries, not just the Top entries

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.