DAX Fridays #12: SUM of distinct values 

You can use calculate and use one measure Sumx(distinct(aaa), calculate( max (bbb) )

Great job! At last, explaining exactly the issue I was experiencing in such a detailed manner! Bravo :)

Hi Ruth, very interesting videos - Thumbs up !!. To have for every function a separate video is really helpful. Hope to see the EARLIER funciton in one of your next tutorials.

Awesome! Thank you for the solution and, more importantly, the explanation of the calc

Really appreciate your work of present failed scenarios and then moving to correct one..Thanks alot

This is a great explanation. Thanks, Ruth.

Thank you very much Ruth! I've strugling with this issue for days, now I can move on.

Thank You!!!!!!!! This video helped me build a Power BI measure that would be equivalent to a Tableau Level of Detail Calculation. Thanks so much!

Fantastic video, and loved the detailed explanation throughout the process!

You are incredible. Im so sad, because the people of my job are changing the power bi to tableau.... But, thanks for all!!! I learning to much about power bi, and you help me to much. thanks thanks thanks

mil gracias por tus tutoriales, me has salvado varias veces

Thank you!!! Just what I needed :)

owh my.. thanks for the video. Been struggling to get this right. Thanks again ! Will continue to subscribe and watch you other videos.

Excellent video, not only for the technical concepts also for sharing your personal learning experiece.

Thank you for demystifying this!

Thank you for the detailed explanation!

Very well put together. Thank you.

Thanks so much - I'm fairly new to DAX and this has exactly addressed a problem I had.

You saved my life with this

Thanks Ruth! I use below mentioned two DAX and get the correct result , plz validate once :- 1st Measure :- Distinct Sum = SUMX(DISTINCT('Budget Table'[PartDenom]),MAX('Budget Table'[Budget Cost Tooling])) 2nd Measure :- Correct Distinct Sum = SUMX(DISTINCT('Budget Table'[PartDenom]),'Budget Table'[Distinct Sum]) Thanks

GREAT explanation. Exactly what I was looking for. Is it possible to save Max Tooling Cost as variable in the formula or it has to be as new measure only?

Thanks for this video Ruth... I was looking for this solution for a long time.

You saved meeee thanks a lot for this!!!

Very good!

Thank you so much Ruth!! I am new to powerBI and struggles with this for an entire day before I could find this video! many many thanks! Subscribed to your channel now :0 I have seen a few other videos of your too and they are awesome!!

Dear you have saved my life!!!! i was looking for something like this several weeks ago!!!! muak!!!

Thanks for another excellent video. I tried your formula, but using a variable for Max of tooling cost instead of a separate measure, and it doesn't work. Do you know why?

thanks so much Ruth, You're the perfect teacher.

I was trying all day how to do it, thanks. and thanks for curbal Data Labs too.

Ruth, I found this video and it is almost perfect. However, I have where my items can have the same cost. So in your example, if I added two more items, say, Casing 1 and Casing 2 and they have the same price ... what happens with your formula given, it only counts the single price for both, rather than two of the same price. Would you know how to improve this calculation where you can still sum the total without dropping out items with the same price? Thanks so much!

Thank you so much ❤

Hi, Thanks for the video, finally I was able to work around. But, I am having one issue. Even though sumx function and Max function gives correct values in each rows, the visual total is different. Any help with that..

Very good!! Simple but fantastic!! Congratulations

Great 'drill down' explanation of each function.

Thank you! Really useful!

Fue de muchas ayuda :D

Hi Ruth, Is there any way to get the same result without using SUMX ? I'm working on a project where i have the same scenario. I have to calculate the SUM of Distinct values. i used the method which is shown in this video and that really works also but since my data is really huge the SUMX calculation is causing it slowdown. I'm facing this problem since last week. tried searching on internet and found several references to Distinct count calculations, but none seems to give me the result that I require.

Great!

Nice and Well explained

Great video. This video just solved my problem. Thanks Ruth

Thanks you solved my problem

very useful... Thanks a lot....

Thank you a lot, Ruth. I have a question though. I understand totals, but I didn´t get why in max sum of toolcost we have a correct values in the filter (in visualizations). I mean, shouldn´t it be 1234 for every row in the visualization? Thank you.

No hay dudas, cada vez que tengo una duda, acudo aquí y pam, se soluciona. Gracias

Gracias!!! Ruth trabajare en este interesante planteamiento...

Very good! a query, from the example if I want to count all the different numbers, what dax function would you use? Example: Band = 1, Casing = 1, Mechanis = 1, Screw = 0. Thank you

would a variable that holds max of each tool would work ? instead of a measure that's referenced later ?

Ruthlessly decimated the problem !!

Hi Curbal. please answer my question... Why cannot be use CALCULATE with Values to sum unique values... CALCULATE(SUM(TOOLING COST)),VALUES(TOOLING COSTS)) ... My idea is in calculate , the second parameter is filter...so let us use values dax...so it will pick tooling costs without duplicates and then as per first criterial, it should be sum.....i thought this way but calculate does not seem to be working..it still picking all values....and not unique

Yet another fabulous video.

I've been stuck trying to do something very similar for hours. I owe you a 🍺

you save my day

Hi. I dont really understand , why when sumx(distinct(table),max(column)) cant success, but when we write a measure outside for abc=max(column) and then sumx(distinct(table),abc), it will work?

Very well explained, thanks! I have a question. I'm using this (and it works), but I want to use this in combination with calculate and the All function. This all function does not work with this unfortunately. And I really can't find why not. Do you have a solution? Thanks in advance and keep up the good work!

Thank you!!

Thanks....I was stuck at this for two days....

Many thanks, I really tired to fid correct formula, appreciate

I really dont understand it.Distinct means it gives the column of unique values. But then it sums it up like there is no distinct. Using the Max function seems like a workaround.

Can we achieve the sum of distinct values through any other way as SUMX() does not work in Direct Query option of Power BI?

thanks, my problem solved

in a hope that you reply to my question.....i again repeat...why cannot we use calculate using values here in a table which does not have any filter...CALCULATE(SUM(TOOLING COST)),VALUES(TOOLING COSTS))

What screen recording software are you using, the quality is fantastic

Thanks for amazing video! So, I have a question. Why it´s different working with an outside measure using "max" that using it inside of the mean measure? Thanks in advance!

First of all: Ruth i hope you are doing fine again after your head injury! Secondly: Oh my god i just had 1.5 hour worth of reaction typed out and chrome crashed :D Thirdly: Ruth, can you please please please always refer to contexts in these cases? Cant say it enough but its vital for many people to develop a sense of really understanding. I also still struggle. Anyway. I give it a try. And i could even still be wrong, as context differentiation is a bit of a hassle :P distinct = sumx(DISTINCT(Table1[type]);max(Table1[cost])) is totally not the same as distinct2 = SUMX(DISTINCT(Table1[type]);[maxcost]) where [maxcost] = max(Table1[cost]) Which you correctly demonstrated in the video. However, you did not say why this was the case and this is one of the most confusing things out there. Because they really look to do the same thing. The reason is that a measure implicitly adds a nested calculate(). distinct3 = SUMX(DISTINCT(Table1[type]);CALCULATE(max(Table1[cost]))) In turn, this is in this example the same as: distinct4 = SUMX(DISTINCT(Table1[type]);CALCULATE(max(Table1[cost]);FILTER(ALLSELECTED(Table1);EARLIER(Table1[type])=Table1[type]))) So what happens? Lets check the calculate() part first. With max(table1[cost]) you literlly ask for the maximum of all rows with the filter context that is shown in argument 2 of the calculate function. This filtercontext is dictated with the filter() function. Filter() selects all rows from the filter context allselected(). The filter(allselected(...)) is not even really needed here as the 2nd part of sumx already (implicitly again) did this, but for completeness sake: it disregards any column and row filters. So basically you end up with the complete dataset unless you have put a sneaky slicer somewhere that we dont know about. Then, you select from the entire filterset only those rows where the type (e.g. 'band') matches the iterator. Lets take a closer look. We basically have 2 parts within the filter() function. Note that within the filter we are in ROW context. So only one row at a time. (A) EARLIER(Table1[type]) = (B) Table1[type] The first part is about the context BEFORE the whole filter(allselect()) context. So basically the iterated item. The second part is IN the row context as this is part of the filter. It contains all rows Then it functions like: 1- For every unique item (distinctcount) 2- Calculate the maximum value... 2b- For all rows in the current table context filter(allselected(...); 2c But only when the rows of this context match the value as given by the iterator So in the total set, the context is determined for every unique item in the iteration, then the maximum is determined, and you add it to the total for every unique item. This in contrast to: distinct = sumx(DISTINCT(Table1[type]);max(Table1[cost])) Which basically does: 1- For every unique item (distinctcount) 2- Return the maximum value from the table context So in the total set, the table context is full set, and you add it to the total for every unique item. *still confused :P

Thank you so much, what a good video, but why doesn't it work for me when there are many blank values?

Thank you so much! it's very helpful. If the value in the third row of column Budget cost tooling is 50 and the value in the fifth row of column Budget cost tooling is 100. How do we make sum total on PartDenom. Thanks alot

I am sorry but I am totally lost still... If I want to do a simple sum, I have to still add distinct and evaluate and max statement to get one calculation? I dont get it.

Is there any other solution without using SUMX Because SUMX is very slow

Is there a way to get this done in one step, instead of two additional measures. I have a case where I need to fix sum of distinct values for about 500 measures. If this is two step, process, I have to additionally create another 1000 columns. I bet there should be a way to do this with one additional measure

How to do summation of first n numbers in a series??? Any idea? Series: { 1, 3, 6, 11, 17, 2, 8} Desired Sum of first 3 numbers: 1+3+6 How to do this in power bi

SUMX(DISTINCT('Table'[partdenom]),SUM('Table'[Budge cost toling])/COUNT('Table'[partdenom])) we can use this simple

Hi Ruth, nice video. but I just feel abit confused: I dont understand correct distinct sum measure is working but max sum of tool cost measure is not. To me they are exactly the same as [max tooling costs] you are using is just an expression of the second part in max sum of toolcost measure...

Спс!!! ✔

I got another way to solve the problem UniqSum = Divide (sum(table [Part] ), counta (table [part]) ), I know this sounds silly but it works!!

I had to wait a lot till I get the right dax, why you didn't start with the right one from the beginning ?

Hey Ruth, This one is really appreciated... I am also looking for 95% Confidence interval band visualization... any suggestion ?? Thanks for help !! Mohit.

Link doesn't work for sample pbix

Thanks dear from last 2 days i was searching for this solution :)

the Max measure which list the max cost could be Max or distinct, the result is the same

we can use this measure too: SUMX(DISTINCT(Table1[PartDenom]),CALCULATE(MAXX(Table1,Table1[Budget Cost Tooling])))

Excellent video, Ruth. Gives us an idea of DAX's powerful flexibility to suit a particular business scenario. The key is to define two measures to take care of filter context. Wondering if Calculate can do some magic with single measure (Food for thought!). I had come across similar problem few months back and Peter Albert's answer to a post on stack overflow helped me. stackoverflow.com/questions/22613333/dynamic-sum-in-dax-picking-distinct-values

Not simple to follow, very discouraging. Can this not be simplified?