Computing totals by summing visible values in DAX

[@cesart4257]

An absolute beast. SQLBI is really the best page out there for DAX. The fact this video was made 3 years ago is also insane. Congrats!

[@ilanapregen1684]

i love you. you have helped me so much, not just with this video, but with countless others, teaching me how to do things I NEVER would have figured out on my own. Seriously, blessings upon blessings onto you.

[@visheshjjain]

I was struggling for 3 days with this and it took me 2 mins after watching this to get what I needed. Thanks!!!

[@allejulien645]

This is probably one of the most usefull tutorial i've ever seen in my entire life, trully

[@misaelpadilla7012]

I wasted a lot of time trying with summarize and then I found this video, just amazing

[@OA-lx2zr]

This is a life saver video.... Been having a problem with a particular report for days and this just saved me!!!!!!

[@arturziolkowski1705]

You saved me hours of trials. I'm happy that I found this video.

[@jisuskamilov]

Thanks for the video. I wish I could find this video before 3 days of suffering🙂

[@oliveroshea5765]

Excellent. Thank you Alberto and Marco for posting these videos

[@ericet19]

Thank you for this, it solved my problem ! It feels like magic when everything adds up properly. Thanks again.

[@mayuranpara4734]

One of the best. Was struggling for couple of days. Thanks a lot.

[@Dianamort09]

Thank you! I was able to figure it out because of this explanation 🎉

[@Tastenmann]

Great elegant solution to my current issue 👍 Love you guys

[@felipesignorellireis7839]

That is exactly what i needed . Thanks Alberto.

[@thunderingravi]

Thank you so much for the short video... This really helped me to solve my problem

[@jessamhan]

Hi Alberto, in the final expression around the 3:43 mark why is the outer CALCULATE necessary? Both the Sales Amount measure and the COUNTROWS expression are already embedded within one, so the context transition from the SUMX iterator will happen anyway.

[@SQLBI]

True, the outer CALCULATE can be removed in this case. However, the good news is that the outer CALCULATE does not affect performance, it just makes the code more verbose and can be avoided.

[@pedrohca]

This video is life saving! Thank you so much

[@JonatasBeque]

You saved my day! Thank you so much!

[@poorak3]

Great video! Helped me a lot. Thank you!

[@JonathanCHANAL]

Hi, I feel like the first CALCULATE is not useful here since the context transition is already there (use measure + other CALCULATE). What do you think of this code: COUNTROWS(FILTER('Date', 'Date'[Working Day]="Workday" && [Sales Amount])). Thanks

[@HennessysXO]

What if I want to compute the null values as part of a total , can the measure alternate between both totals when users selects month with values (not null) vs months with values (including null) ?

[@sotork]

Ciao Alberto, huge fan of your work here!...I think the video is repeated in the playlist.

[@joaquimcosta952]

I already knew this calculation, but sometimes I like to review some calculations and I always come to the conclusion that many courses are inspired by your material. It is the path to immortality. I'm just sorry that the number of "LIKES" doesn't reflect the number of views. It is the least we can do to acknowledge the excellent work that Alberto and Marco have been doing for years and that many people have helped. How difficult can it be to make "LIKE" ???

[@SQLBI]

Thanks!!

[@yannickfranckum6589]

Exactly what I was looking for! Thanks for sharing

[@UweSeidel-d1n]

Awesome, thanks for sharing!

[@lucasgv]

Salved my life! Thanks a lot!

[@goldwhispers]

Do you have other videos similar to this, im stuck on a similar'ish problem. I am trying to average a division at the total level but its not working.

[@lionels839]

Hello Is the standard SUMMARIZE or ADDCOLUMNS + SUMMARIZE technique equivalent?

[@SQLBI]

It's better to use ADDCOLUMNS+SUMMARIZE - see www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

[@growlife4779]

Is there a way to do the following I created a measure to get proportion of sale per entity divided by the total sales across all entities. Then I wanted to create a measure called other that groups all entity sales that are below 3% into one summed up value. using alteryx what you can do is just a formula tool that can write new column called comment and get it to comment other in row if below 2% total sales and then just filter out comment other and sum it to get a one value called other. I was just trying to figure out how to sum of a measure that is not based on values in a table but also another measure? If i can create a calculated column that just pulls in the values when its below 3% I can just sum the column in that table. But as its a measure I cannot use a sum or sumx. I tried to get the measure into a calculated column but it won't let me because the rows have different values and it needs to be one.

Grazie Maestros!!! Grazie per Tutti !!

[@sivamani9752]

Super, it would be helpful if you show how sort the columns based on the grand total in the bottom. Eg: left coulum with customer name, top columns are with few sales rep names and in the table it will have each sales reps sales amount for each customer, now i want to sort the sale rep based on the total sales done by each rep( total in the bottom)

[@mrgreenteatime]

For all those people arriving from excel environment I wish you good luck a lot of petience :)

[@itskshitijaa]

Hello Sir! This was insightful Thankyou! I however am facing an issue if anyone can help. You see, I have one Measure(Hrs Above Base) where I simply calculate difference between two other measures of the same table (Actual). Now, when the result of difference is negative I want to set it to zero. It is doing exactly this But then the Total seems to be incorrect still. I am iterating on the resources here. Am I missing anything here ?

[@emilyamalkin4367]

Ask your question on Power Bi Community site - you will get an immediate response (or several!)

[@atullo1423]

What would the PBI developers be without SQLBI. THANKS A LOOOTT!!!

[@lionels839]

Hello This formula does not work When you want to average visible values

[@NikitaShilyaev]

but if ever we want to show this table say by weeks, not by months, we should create another measure?

[@Kmsbi]

Thank you so much for sharing. you are awesome

[@1yyymmmddd]

This works with simple data models. Should you have more complex calculations computing data from different tables the issue becomes much more challenging.

[@radicholakov9588]

Great video! It really helped me out. However I'm struggling to make it work when trying to iterate on more then one column by using summarize columns. Any tips how to make it work as if in the example placing the year and month in separate columns?

[@SQLBI]

You should iterate the combination of the two columns - it requires an extra step, for this reason a single column with year and date is better (and it works also as hidden column, the user can choose two columns). The simpler, the better (and also the faster): SUMX ( VALUES ( 'Date'[YearMonth] ), ) SUMX ( SUMMARIZE ( 'Date', 'Date'[Year], 'Date'[Month] ), )

[@aleksanderstrommer7677]

I am still waiting the day when I have fun with DAX...

[@SQLBI]

Yes, you could use NOT ISBLANK ( ).

[@mathew9665]

Nice - if you can have sales value of both positive and negative, I would presume your if condition instead of >0 would isnotblank ?

[@SQLBI]

Yes you can use NOT ISBLANK ( )

[@emmanuelagbo218]

Having a date table with the min and max date of the sales table would have prevented this error. Though I learnt something new.

[@ExcelandPowerBISinhalen]

How do you zoom in and out in Power BI?

[@SQLBI]

You can use ZoomIt (free) or GlassBrick screen magnifier (licensed). We use both, depending on the context.

[@ExcelandPowerBISinhalen]

@@SQLBI Amazing Thank you very much.

[@oliveroshea5765]

@@SQLBI more context 😂

[@DIGITAL_COOKING]

@@SQLBI yeah! , there is context for everything 🙂, great video Alberto you give me an idea about calculation i had in the past

[@susantakumar8330]

Nice 👍

[@dominikreim7723]

Best way is to use a Power Query generated Index column for the Values function

[@nazmulalam2046]

Thanks its helpful

[@RichArd-ee2qj]

Ahhhhhh...... Why does this work? I plagiarized the code. It corrected a similar problem I was having. Pax LY = SUMX ( VALUES ( '_Calendar'[Date] ), CALCULATE ( IF ( ISBLANK ( [Pax] ), BLANK (), CALCULATE ( [Pax], SAMEPERIODLASTYEAR ( _Calendar[Date] ) ) ) ) )

[@anshulroxx6791]

But then your measure will only give the correct subtotals for the field defined in DAX VALUE() {Calendar Year Month} If we pull some other columns lets say Department level we will get incorrect totals. Whats your take on this?

[@markuzz1982]

Thanks a lot! Problem solved!