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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX FOR SUMX(DISTINCT(COLUMN))

Hi All,

 

I need to add all distinct values of a column based on a filter. I have tried out this formula but it is throughing an error...

 

formula that i applied:

CALCULATE(SUMX(DISTINCT(SAVINGS,SAVINGS[AMOUNT])),FILTER(SAVINGS,SAVINGS[DATE2]="21-MAY")
 
Please suggest me proper formula to apply sumx(distinct(column_values))... not only for one table.... suggest me for multiple tables in one query itself.
 
Thanks,
Gowthami T M
4 REPLIES 4
amitchandak
Super User
Super User

When you want to do both count and sum both then means there should some group by or level till which you will count post that you will sum. summarize helps you in such case

sumx(summarize(FILTER(SAVINGS,SAVINGS[DATE2]="21-MAY"),SAVING[Some ID],"_dis",DISTINCTCOUNT(SAVINGS,SAVINGS[AMOUNT])),[_dis])

 

This SAVING[Some ID] can one column or more and can come from one more related table.

Please see an example. Here we wanted to count customer shown growth

Count of Cutomer  = 
var _date = MAXX(OrderTime,OrderTime[Order Date])
var x= 1
return
CALCULATE(COUNTX(SUMMARIZE(Sales,Sales[Brand],Sales[Customer ID],"ytd",TOTALYTD(sum(Sales[Sales]),'Order'[Order Date],"12/31"),"LYTD",CALCULATE(sum(Sales[Sales]),SAMEPERIODLASTYEAR('Order'[Order Date]))),if([ytd]>[LYTD],Sales[Customer ID])))

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

Resolvi um problema parecido, usando a dica do amigo, realizei o calculo do KM rodado de uma viagem.

esse se multiplicava conforme a quantidade de documentos que havia dentro do manifesto, com a seguinte função trouxe apenas uma informação isolada de cada coluna e realizei a soma:

Km Rodado = SUMX(SUMMARIZE(F_ReceitaDespesa;F_ReceitaDespesa[CODMAN];"Km Final";VALUES(F_ReceitaDespesa[KMTFIN]);"Km Inicial";VALUES(F_ReceitaDespesa[KMTINI]));[Km Final]-[Km Inicial])
Anonymous
Not applicable

Measure=
Var a= summerize(table,table[column],table[filterColum])
Return
Sumx(a,if(table[filtercolum]="filtervalue",table[column],0))

Table[column] should repleace by your column for which you are trying to sum the values.


Thanks
Pravin Wattamwar

If it resolves your problem mark it as a solution and give Kudos.
kentyler
Solution Sage
Solution Sage

It's very hard to answer such a far ranging question " not only for one table.... suggest me for multiple tables in one query itself."

If you can post some sample data, and also the error that your dax code is throwing, we may be able to make some practical suggestions.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors