You saved my life, your an Angel!! Ive been doing a sum formula outside of my pivot table and needed to automated the process. I tried the method in this video but i think its outdated and was not working. this did and im sooo happy. you made my job easier.
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
Just use calculate function to get max "budget cost tooling" for each distinct partdenom, which is then added since we are using sumx function: SUMX(DISTINCT('Budget Table'[PartDenom]),CALCULATE(MAX('Budget Table'[Budget Cost Tooling])))
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.
Actually, I loved your committee regarding your videos, and I really loved (Dax friday Playlist), but I think that you need to prepresent before you go live as something I stuck while the video working but still you one of the Dax heroes 😉
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
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!
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
Thanks for your feedback and enjoy tableau, I heard it is a great visualization tool and has a great community too. You are going to learn a lot and are in for a treat :)
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!
@@islamelhawary6679 I am not sure if it worked in 2016 when the video was posted, but you can write it in one measure as follows: Measure = SUMX ( SUMMARIZE ( 'Table', 'Table'[PartDenom], 'Table'[Budget Cost Tooling] ), 'Table'[Budget Cost Tooling] )
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
Your explanation was particularly useful, as it showed me how to see what is going on with the filter context. Also, I had started off with exactly the first two error stages, so you showed me where I had gone wrong - and the answer was so much simpler than I expected! I had been searching for ages, and found all kinds of really advanced "solutions" that I couldn't get working, then you did it all with MAX and SUMX!
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?
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?
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))
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..
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!!
@@CurbalEN -- Thank you ! I needed to do average next of the same distinct fields and I did averagex function - averagex(table(field),maxmeasure ) but now My average is off...any thoughts on what I am missing?
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?
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...
Hi, I have to watch that video again, but I am guessing that it is all about the filters that are applied on the first and the second measure. Maybe I can do a new video about it. /Ruth
Hey Ruth, i have the exact some doubt as Super D Cinema. For me there is no difference writing the formula (MAX...) or put the measure as it is... isn't the same in a CALCULATE? For instance: CALCULATE(SUM(SALES[Sales Total]).... or CALCULATE([measure that represents the SUM OF SALES Total]);;;; Your videos are great. Cheers from Brazil!
I had the same thoughts. Power Bi is very strange. Max measure works, however, writing the actual formula MAX(Table1[Budget Cost Tooling] does not work in the measure called 'Max sum of tooling. very confusing. but great video.
MAX is a aggregte function , it does calculation on entire column instead row wise. So, if you have A = 10 , B= 20, C = 40 , C = 70 ...using max on numbers will give you 70 . So, this means if you say i need a table using VALUES Dax, which removes the duplicates , your table would look like this ..A = 70, B = 70 , C = 70 .....So, now you see you have no duplicates (c item is coming twice in a table) and corresponding values to each of these items is 70. Why? Because MAX worked on entire list or columns of prices 10,20,40, 70 and came out with 70 as biggest number. So, when you supply this table to SUMX which we use to add is going to add 70 thrice and giving you 70*3 = 210 . Which is obviously wrong because we wanted the maximum value basis each item and not just the one largest number from column. So, we go and wrap around our MAX with CALCULATE. Why? because calculate has a flexibility that it works on filter context, if you see its definition. Now since max is a part of CALCULATE, It will return maximum values only by looking at the item which you have put in VALUES Dax. So, VALUES(PRICE) is returning unique items like A,B,C and against these items, CALCULATE(MAX) now giving maximim value basis item . So, it has now A = 10, B = 20 , C = 70 ..this is finally used in SUMX so we have a correct answer.. Calculate always takes care of filters which are availble in table. So you have now maximum value basis filters which are A,B,C
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
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
Hi Anna, There are always many ways to do the same thing in DAX. To know what is best for your model , post sample data in the power bi community to get detailed help. Have a great weekend! /Ruth
Hi Abhishek, SUMX will work with Direct Query if you make the following change in your Power BI Desktop file: File -> Options and then Settings -> Options -> Direct Query ->Allow unrestricted measures in Direct Query mode. /Ruth
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.
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.
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.
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
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.
Hey Ruth, This one is really appreciated... I am also looking for 95% Confidence interval band visualization... any suggestion ?? Thanks for help !! Mohit.
I dont have a video on that, but check the power bi community , I am sure somebody must have done something similiar and can give your some pointers! /Ruth
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
It was a long read. I know that. But it definately was not a question. It was an answer on a question from a user on your post which i addressed, plus a remark to you :)
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!
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
+Deepak Agrawal Hi Deepak, I had the same problem too when I was a beginner and I too got help with a measure that worked but I didn't fully understand it, so that is why I did this video, hopefully it helps others... If you try with calculate and you succeed, let us know! /Ruth
Hi Ruth, CALCULATE has done it again. You can use the following measure: Correct distinct sum = SUMX ( DISTINCT ( Table1[PartDenom] ), CALCULATE ( MAX ( Table1[Budget Cost Tooling] ) ) ) Meanwhile, Let us all bow to CALCULATE (:
You can use calculate and use one measure
Sumx(distinct(aaa), calculate( max (bbb) )
You saved my life, your an Angel!! Ive been doing a sum formula outside of my pivot table and needed to automated the process. I tried the method in this video but i think its outdated and was not working. this did and im sooo happy. you made my job easier.
This is amazing
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
Just use calculate function to get max "budget cost tooling" for each distinct partdenom, which is then added since we are using sumx function:
SUMX(DISTINCT('Budget Table'[PartDenom]),CALCULATE(MAX('Budget Table'[Budget Cost Tooling])))
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.
+Victor Friesen Thanks Victor! I will do some videos on time intelligence first, but after that EALIER is next :)
/Ruth
Dear you have saved my life!!!! i was looking for something like this several weeks ago!!!! muak!!!
🥳🥳🥳
/Ruth
@@CurbalEN you saved my life as well!! thank you so much, amazing explanation!
@@carolinabruno22 🥳🥳🥳
/Ruth
Actually, I loved your committee regarding your videos, and I really loved (Dax friday Playlist), but I think that you need to prepresent before you go live as something I stuck while the video working but still you one of the Dax heroes 😉
Really appreciate your work of present failed scenarios and then moving to correct one..Thanks alot
Thank you very much Ruth! I've strugling with this issue for days, now I can move on.
Smooooooth
Great job! At last, explaining exactly the issue I was experiencing in such a detailed manner! Bravo :)
🥳🥳
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
thanks so much Ruth, You're the perfect teacher.
Thanks!
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!
mil gracias por tus tutoriales, me has salvado varias veces
Todo un placer!
Ruthlessly decimated the problem !!
🤣 My parents knew what they were doing from the start!!
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
Thanks for your feedback and enjoy tableau, I heard it is a great visualization tool and has a great community too. You are going to learn a lot and are in for a treat :)
You saved my life with this
Thanks dear from last 2 days i was searching for this solution :)
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!
I have the same question...? :(
I have to check the video to understand the question and my time is soooo limited... I will try on Monday.
@@CurbalEN I've the same question
@@islamelhawary6679 I am not sure if it worked in 2016 when the video was posted, but you can write it in one measure as follows:
Measure =
SUMX (
SUMMARIZE (
'Table',
'Table'[PartDenom],
'Table'[Budget Cost Tooling]
),
'Table'[Budget Cost Tooling]
)
I've been stuck trying to do something very similar for hours. I owe you a 🍺
I could use one right now ;)
Excellent video, not only for the technical concepts also for sharing your personal learning experiece.
Thanks !
This is a great explanation. Thanks, Ruth.
owh my.. thanks for the video. Been struggling to get this right. Thanks again ! Will continue to subscribe and watch you other videos.
Glad it was helpful!
Awesome! Thank you for the solution and, more importantly, the explanation of the calc
SUMX(DISTINCT('Table'[partdenom]),SUM('Table'[Budge cost toling])/COUNT('Table'[partdenom]))
we can use this simple
would a variable that holds max of each tool would work ? instead of a measure that's referenced later ?
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
+Ricardo Tito Hi Ricardo,
Not sure what you mean, but I have a count video that might help you?
m.th-cam.com/video/V8wYTjKyvgk/w-d-xo.html
/Ruth
No hay dudas, cada vez que tengo una duda, acudo aquí y pam, se soluciona. Gracias
Muchas gracias por el feedback :)
/Ruth
Thanks so much - I'm fairly new to DAX and this has exactly addressed a problem I had.
Fabulous!!
Your explanation was particularly useful, as it showed me how to see what is going on with the filter context. Also, I had started off with exactly the first two error stages, so you showed me where I had gone wrong - and the answer was so much simpler than I expected! I had been searching for ages, and found all kinds of really advanced "solutions" that I couldn't get working, then you did it all with MAX and SUMX!
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?
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?
Very good!!
Simple but fantastic!!
Congratulations
Great to hear!
@@CurbalEN
I'm brazilian. I need to improve my English. I love your classes.
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))
Thanks....I was stuck at this for two days....
Thank you so much, what a good video, but why doesn't it work for me when there are many blank values?
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..
I was trying all day how to do it, thanks. and thanks for curbal Data Labs too.
My pleasure :)
/Ruth
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!!
Welcome and I hope more videos will be useful to you!
@@CurbalEN -- Thank you ! I needed to do average next of the same distinct fields and I did averagex function - averagex(table(field),maxmeasure ) but now My average is off...any thoughts on what I am missing?
@@lavanisari so many things it could be...share all the details with the power bi community to get personalized help!
Thanks for this video Ruth... I was looking for this solution for a long time.
Just on time ;)
/Ruth
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?
Great video. This video just solved my problem. Thanks Ruth
Perfect and have a great sunday!!
/Ruth
I am still not getting the correct result!!!! (None of them worked.... getting total wrong)
MaxSales = max('TA tele_cascade'[Sales Calls])
Total_Sales Calls =SUMX ( distinct('TA tele_cascade'[Agent] ) ,[MaxSales] )
*********************************************************************************************
Total_Sales Calls =
SUMX ( Distinct ( 'TA tele_cascade'[Agent] ) ,CALCULATE ( MAX ( 'TA tele_cascade'[Sales Calls] ) ) )
Post your case in the power bi community and make sure you give some sample data.
/Ruth
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...
Hi, I have to watch that video again, but I am guessing that it is all about the filters that are applied on the first and the second measure.
Maybe I can do a new video about it.
/Ruth
Hey Ruth, i have the exact some doubt as Super D Cinema. For me there is no difference writing the formula (MAX...) or put the measure as it is... isn't the same in a CALCULATE? For instance: CALCULATE(SUM(SALES[Sales Total]).... or CALCULATE([measure that represents the SUM OF SALES Total]);;;;
Your videos are great.
Cheers from Brazil!
Measures have an implicit calculate that helps evaluate the row context.. thats y the measue max total cost works and the max(total cost) not work.
I had the same thoughts. Power Bi is very strange. Max measure works, however, writing the actual formula MAX(Table1[Budget Cost Tooling] does not work in the measure called 'Max sum of tooling. very confusing. but great video.
MAX is a aggregte function , it does calculation on entire column instead row wise. So, if you have A = 10 , B= 20, C = 40 , C = 70 ...using max on numbers will give you 70 . So, this means if you say i need a table using VALUES Dax, which removes the duplicates , your table would look like this ..A = 70, B = 70 , C = 70 .....So, now you see you have no duplicates (c item is coming twice in a table) and corresponding values to each of these items is 70. Why? Because MAX worked on entire list or columns of prices 10,20,40, 70 and came out with 70 as biggest number. So, when you supply this table to SUMX which we use to add is going to add 70 thrice and giving you 70*3 = 210 . Which is obviously wrong because we wanted the maximum value basis each item and not just the one largest number from column. So, we go and wrap around our MAX with CALCULATE. Why? because calculate has a flexibility that it works on filter context, if you see its definition. Now since max is a part of CALCULATE, It will return maximum values only by looking at the item which you have put in VALUES Dax. So, VALUES(PRICE) is returning unique items like A,B,C and against these items, CALCULATE(MAX) now giving maximim value basis item . So, it has now A = 10, B = 20 , C = 70 ..this is finally used in SUMX so we have a correct answer.. Calculate always takes care of filters which are availble in table. So you have now maximum value basis filters which are A,B,C
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
+Duy Diep Hi Duy, not sure what you mean, can you explain a bit more?
/Ruth
Thanks Ruth, I think i figured out it, In your measure just chose the right distinct column we want. anyway thanks alot
Great 'drill down' explanation of each function.
Thanks Deepak!
/Ruth
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
Hi Anna,
There are always many ways to do the same thing in DAX. To know what is best for your model , post sample data in the power bi community to get detailed help.
Have a great weekend!
/Ruth
Thanks Ruth
Have a great weekend Anna :)
/Ruth
You can use this UniqSum = Divide (sum(table [Part] ), counta (table [part]) ) and replace sum with sumx for iteration purpose
Thank you!!! Just what I needed :)
Can we achieve the sum of distinct values through any other way as SUMX() does not work in Direct Query option of Power BI?
Hi Abhishek,
SUMX will work with Direct Query if you make the following change in your Power BI Desktop file:
File -> Options and then Settings -> Options -> Direct Query ->Allow unrestricted measures in Direct Query mode.
/Ruth
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.
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!
Yey!!! Glad it helped :)
/Ruth
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.
Thank you for demystifying this!
+David Ocampo You welcome!! :)
/Ruth
Very well put together. Thank you.
Thanks!
What screen recording software are you using, the quality is fantastic
Thanks Justin, Camtasia 8 :)
/Ruth
Yet another fabulous video.
+Cloud Hound Thanks Christopher for your comment and all the shares! Truly appreciate it :)
/Ruth
Cloud Hound ,
Fantastic video, and loved the detailed explanation throughout the process!
Thanks Edward!
/Ruth
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.
Gracias!!! Ruth trabajare en este interesante planteamiento...
+Norberto Vera Reatiga Curioso que algo tan sencillo teóricamente, sea tan complicado, verdad?
/Ruth
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
Nice and Well explained
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.
You saved meeee thanks a lot for this!!!
Hey Ruth,
This one is really appreciated...
I am also looking for 95% Confidence interval band visualization... any suggestion ??
Thanks for help !!
Mohit.
I dont have a video on that, but check the power bi community , I am sure somebody must have done something similiar and can give your some pointers!
/Ruth
Is there any other solution without using SUMX
Because SUMX is very slow
I had to wait a lot till I get the right dax, why you didn't start with the right one from the beginning ?
“Tell me and I forget,
teach me and I remember” - Benjamin Franklin
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
Hi Dennis, feeling perfect thanks!
Could you post the question in the power bi community? Curbal is on vacation now :)
/Ruth
It was a long read. I know that. But it definately was not a question. It was an answer on a question from a user on your post which i addressed, plus a remark to you :)
Oh, in that case, thanks!!
I am on vacation mode :)
/Ruth
Put awaaaaaaaayyyy the phoneeee
😂😂
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!
we can use this measure too: SUMX(DISTINCT(Table1[PartDenom]),CALCULATE(MAXX(Table1,Table1[Budget Cost Tooling])))
Thanks for sharing!
/Ruth
@@CurbalEN very nice my dear thanks so much
Can you explain This step by step...if Max is used it should return One value only...if I want total of each item but only their max value should sum
Thank you for the detailed explanation!
🎉🎉
/Ruth
Thank you so much ❤
Many thanks, I really tired to fid correct formula, appreciate
Very good!
you save my day
Music to my ears 😊
/Ruth
Thanks you solved my problem
the Max measure which list the max cost could be Max or distinct, the result is the same
I got another way to solve the problem UniqSum = Divide (sum(table [Part] ), counta (table [part]) ), I know this sounds silly but it works!!
Great !
/Ruth
Link doesn't work for sample pbix
All links are here now:
curbal.com/donwload-center
/Ruth
Thank you! Really useful!
Yey!!
/Ruth
thanks, my problem solved
Wonderful!
very useful... Thanks a lot....
Thank you!!
🥳🥳
🥳🥳
Fue de muchas ayuda :D
Great!
Not simple to follow, very discouraging. Can this not be simplified?
Sorry to hear that, there are a lot of resources on DAX, check them out as they might explain it better.
Don’t give up!
/Ruth
Спс!!! ✔
с удовольствием!
/Ruth
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
+Deepak Agrawal
Hi Deepak,
I had the same problem too when I was a beginner and I too got help with a measure that worked but I didn't fully understand it, so that is why I did this video, hopefully it helps others...
If you try with calculate and you succeed, let us know!
/Ruth
Sure. Thanks. (:
Hi Ruth,
CALCULATE has done it again. You can use the following measure:
Correct distinct sum =
SUMX (
DISTINCT ( Table1[PartDenom] ),
CALCULATE ( MAX ( Table1[Budget Cost Tooling] ) )
)
Meanwhile, Let us all bow to CALCULATE (:
+Deepak Agrawal
Smart! And thanks for sharing! :)
/Ruth
+Deepak Agrawal Oh! One more thing, now you need to explain why that works!
;)
/Ruth