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
EricaD
New Member

Calculate SUMX ignoring slicer filter

Hi, 

I need to calculate ROA for each company and then sum those values ignoring slicer filter for companies. I just need one number that will not change while using filter for companies. I tried ALL, REMOVEFILTERS, ... nothing worked.  Can anybody help me? 

 

This is what I got: 

ROA =  
var income = CALCULATE(SUM(Table[NUM_VALUE]), FILTER(Table, Table[C_EP] = "BCE")
var assets= CALCULATE(SUM(Table[NUM_VALUE]), FILTER(Table, Table[C_EP] = "CE")
return income/assets

TOTAL_ROA = 
var p = MAX(Table[PERIOD])
var a = SUMX(
   SUMMARIZE(Table,Table[SUBJECT], "ROA",CALCULATE(Table[ROA], Table[PERIOD] = p)),
CALCULATE(Table[ROA], Table[PERIOD] = p))
return a
 
Thanks for all your help. 
1 ACCEPTED SOLUTION

Hi @EricaD 

Try this measure

TOTAL_ROA = 
var d = MAX(TABLE1[PERIOD])
RETURN

SUMX(
    SUMMARIZE(
        ALL(Table1[SUBJECT]),
        Table1[SUBJECT], 
        "ReturnOnAssets", 
        CALCULATE(
            [ROA],
            Table1[PERIOD] = d
        )
    ), 
    [ReturnOnAssets]
)

 

littlemojopuppy_0-1659449723631.png

littlemojopuppy_1-1659449747017.pnglittlemojopuppy_2-1659449765003.png

 

 

View solution in original post

5 REPLIES 5
EricaD
New Member

Hi @littlemojopuppy, thanks for your quick response. I can give you .pbix and also .xlsx: Files 

I just want TOTAL_ROA to be 469% no matter what I select in filter for subject.

Hi @EricaD 

Try this measure

TOTAL_ROA = 
var d = MAX(TABLE1[PERIOD])
RETURN

SUMX(
    SUMMARIZE(
        ALL(Table1[SUBJECT]),
        Table1[SUBJECT], 
        "ReturnOnAssets", 
        CALCULATE(
            [ROA],
            Table1[PERIOD] = d
        )
    ), 
    [ReturnOnAssets]
)

 

littlemojopuppy_0-1659449723631.png

littlemojopuppy_1-1659449747017.pnglittlemojopuppy_2-1659449765003.png

 

 

@littlemojopuppy Thank you soooo much, you literally saved my life! 😄
And I really don't know why I haven't try that one. 

HI @EricaD.  Glad I could help!  🙂

littlemojopuppy
Community Champion
Community Champion

Hi @EricaD.  Very strange that ALL and REMOVEFILTERS didn't work...  Can you provide some sample data to work with?

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.