cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RADHIKA_3022
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
v-shex-msft
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.
amitchandak
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!

AllisonKennedy
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. 



?? Check out my March Madness Report??


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
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

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.