cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Usage of SUMX and its syntax

Can anyone please explain the below DAX? When SUMX is supposed to have "Table" as its first argument, below code has column expression as its first parameter. Is it the right usage when the right syntax is supposed to be "Table" as yth

[sales of the top 10 sold products]
= SUMX(
TOPN(
10,
SUMMARIZE(Product, [ProductKey], "TotalSales",
SUMX(RELATED(InternetSales_USD[SalesAmount_USD]),InternetSales_USD[SalesAmount_USD]) +
SUMX(RELATED(ResellerSales_USD[SalesAmount_USD]), ResellerSales_USD[SalesAmount_USD]))
)
)

4 REPLIES 4
Community Support
Community Support

HI @RADHIKA_3022,

Perhaps you can take a look at the following blog, it mentions the usage of 'use related function work as filters':
DAX Filter - RELATED function 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User IV
Super User IV

@RADHIKA_3022 , I doubt you need related .

 

Create two measures like these

 

TotalSales = InternetSales_USD[SalesAmount_USD] +ResellerSales_USD[SalesAmount_USD]

Top 10 City Rank = CALCULATE([TotalSales],TOPN(10,all(Product[ProductKey]),[TotalSales],DESC),VALUES(Product[ProductKey]))

 

For TOPN Refer : https://www.youtube.com/watch?v=QIVEFp-QiOk

 

in case sumx related will come at column name

example

SUMX(Product,RELATED(InternetSales_USD[SalesAmount_USD]))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User III
Super User III

@RADHIKA_3022  Is this your data or sample file? SUMX first argument is a table or table expression, so TOPN returns a table, in your case with 10 rows. 


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Thanks for responding.

But "Related" in the SUMX expression returns a scalar value, which is not a table/ table expression.. when SUMX is supposed to have TABLE of Values as its first argument. Hope you understand my point.

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors