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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
AnonymousPerson
Advocate V
Advocate V

SUMX over calculated-single-value-table to fix all weird subtotals

I have long struggled with the summing up of measures at every level of complex hierarchies that cover multiple dimensions. So of course I've dabbled with the SUMX function becuase that fixes a lot of things. But I've always struggled with how to understand what to put as the first parameter in SUMX. It seems that no matter what value I put for the first parameter of SUMX, sometimes it is good and someties bad.  

 

So I started doing this...

SUMX (
      DATATABLE ( "1", INTEGER, { { 1 } } ),
      CALCULATE (
                 COUNTROWS ( 'Sales Agg Date Store' ),
                 'Sales Agg Date Store'[Sales Above Threshold Flag] = TRUE ()
                 )
       )

 

The important part is the first parameter of SUMX there. It is just a single value single column single row static table. When I write my SUMX like that it adds up among every hierarchy I throw at it. Miraculous. But seems like I'm maybe missing the point of SUMX by hacking it like this. Or am I? Is there a better way to do this? 

 

1 ACCEPTED SOLUTION
MattAllington
Community Champion
Community Champion

From what I can see, the SUMX portion of this formula does nothing compared with removing SUMX all together. The first parameter is a table; a physical table or a virtual table that is typically part of the model (your table is not part of the model).  The table will normally/necessarily have a relationship to the rest of the model or lineage (a virtual relationship in the case of a virtual table) that propagates filters between the first table parameter and the rest of the model, so that the second parameter can do its job. 

it is not possible to answer your specific query because you have not provided any details of the model or the problems you are facing. You may find my article here useful. https://exceleratorbi.com.au/use-sum-vs-sumx/



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

View solution in original post

2 REPLIES 2
AnonymousPerson
Advocate V
Advocate V

Thanks @MattAllington ...  You're right. This measure changed so many times, I totally worked myself in and out of needing a SUMX. 

 

Thanks for the extra set of eyes!!!

MattAllington
Community Champion
Community Champion

From what I can see, the SUMX portion of this formula does nothing compared with removing SUMX all together. The first parameter is a table; a physical table or a virtual table that is typically part of the model (your table is not part of the model).  The table will normally/necessarily have a relationship to the rest of the model or lineage (a virtual relationship in the case of a virtual table) that propagates filters between the first table parameter and the rest of the model, so that the second parameter can do its job. 

it is not possible to answer your specific query because you have not provided any details of the model or the problems you are facing. You may find my article here useful. https://exceleratorbi.com.au/use-sum-vs-sumx/



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.