DAX Fridays! #52: SUMIF, COUNTIF

[@collontomlyn6567]

Thank you for such a clear, concise explanation of how to do SUMIFS in DAX! I've been trying to wrap my head around all these new functions and this video lays it out very clearly how to make it work.

[@CurbalEN]

Excellent to hear!

[@gertvanhelsdingen7617]

Excel users leverage the ability to reference the "YEAR" or the "PRODUCT TYPE" to the left in order to find the totals for all line items, not just the one with the "1996" hard coded value. So for me this was not the equivalent to a SUMIF. Would have liked if you went a bit more in detail.

[@CurbalEN]

Thanks for the feedback! /Ruth

[@judel8535]

Agreed Gert. This was not helpful if you are looking to create the type of dynamic sumif you can place in a column to sumif based on the value in a given field for each row in a table.

[@MarshallMelnychuk]

I find many of your videos very helpful Curbal, but I have to say that I have quickly learned to directly jump to about the three minute mark of any video - ...

[@CurbalEN]

😂😂 That is the most elegant way I have seen to tell me my intros are too long!! Appreciate the effort thanks! ;) /Ruth

[@TheDistractionStudio]

Thank You Ruth! Happy Friday

[@CurbalEN]

+rajan77 Happy Friday Rajan!! /Ruth

[@NorbertoVeraReatigaNVR]

Gracias Ruth!!! Me encanto repasar... buena idea para mantener fresco nuestro conocimiento de las formulas y una buena bienvenida a los que inician, Saludos!

[@CurbalEN]

+Norberto Vera Reatiga Saludos Norberto! /Ruth

[@bhuvanasundar9427]

Hi Folks, I have a table where I want to count number of specific text in a column. How can I do that? My coloumn name is Observation Values there are "SU", "SW", SO. So how can I apply count if?

[@muddaarmy8647]

I'm looking for an answer on this formula that I'm trying to do in Analysis services. I believe it will be DAX as well. I have a list of employees with their respective supervisor ID. How can I calculate the total assigned employees of a supervisor per month (month column is available). Been using Excel for a long time but still quite new to Powerbi/SSAS. Thank you.

[@CurbalEN]

Hi Mudda, Can you please post your question in the Power bi Community? Thanks ! /Ruth

[@BilalZia]

If we want to show 1996 sales in front of 1996 only and blank for other years we can add following as second filter argument in calculate function --> Values(DimDate[Year]) .

[@CurbalEN]

+Bilal Zia Yes or use filter ;) /Ruth

[@mnowako]

Thank you Ruth! Another great video!

[@CurbalEN]

+Mariusz Nowakowski Great you think so!! /Ruth

[@enggsushant]

If we are using ALL function, will it also ignore the filters put on visualisation pane ( Report Filter, Page Filter)?

[@CurbalEN]

Hi, have you seen this video ? m.kzbin.info/www/bejne/p2XNloOer7CnmLc /Ruth

[@sonuaryan5813]

Hi Ruth, I have Seen most of the video It is very helpful to me...You rock :)

[@CurbalEN]

Thanks and so do you :) /Ruth

[@SmartBimson]

Again a very cool video Ruth! Thank you 🌞👍🏻

[@CurbalEN]

+SmartBimson Hi Andy, happy weekend! /Ruth

[@excelisfun]

Cool Excel parallel!!! : )

[@CurbalEN]

+ExcelIsFun I have been asked this many times, so hopefully it is useful for beginners :) Happy Friday! /Ruth

[@excelisfun]

Useful and fun!!!!

[@CurbalEN]

+ExcelIsFun Excellent! :) /Ruth

[@excelisfun]

I think you meant EXCELlent!!!!

[@CurbalEN]

+ExcelIsFun 😂😂 I don't, but now I do!! /Ruth

[@souravsen8861]

Please help !!! If you want to make a cumulative sales chart like in 1996 sales is 40980 and till 1997 total was 40980+115388 and 1998 it is sum from (96-98) how do we do that

[@CurbalEN]

Hi, Can you post in the power bi community with some sample data? /Ruth

[@souravsen8861]

Curbal what power bi community ?

[@CurbalEN]

This one: community.powerbi.com/ /Ruth

[@etf42]

what if you wanted to calculate each year in one table using only one measure?

[@cedvaz1984]

hi i really love your videos, however I;m looking for a solution which I cannot find or maybe im not wording it right in my searches. I want to create a measure which calculates an amount based on a % (not available at a field) if it 2 of more fields meet a criteria. For instance - I want to create a measure that contains 2 conditions and calculates the percentage amount based on a common numeral amoun. example - if Field1 IS ABC Field2=DEC, I want 9% x Field3 and to display in a value Field 1 = USA Field 2 = Apples Field 3 = 1000 The result should be 90 (1000 x 9%) Hope this makes sense ?

[@konstantinoskoulocheris9945]

Hi, I'm a bit new to DAX and I've been trying to count X values from a column that contains more than one values in each cell (it can be "X|Y|Z" or "Y|X|Z" for example, in a random order). If I was in excel that would have been solved with a =COUNTIF(Table[Column],"*"&"X"&"*") and would get the number of times "X" appears in that column. I have been trying to get that using various combinations but had issues with actually getting an expression to work. Any help would be much appreciated! Kostas

[@TryAthletic]

HI this is great!, but if I want to do a sum IFS based one more than one criteria how can I go about that? I tried adding an and function but this dident work sum if 1996 and 1997 = Calculate (Sum([Order_details Tot sales]), AND(dimDate[Year]="1996,DimDate[Year]=1997"))

[@CurbalEN]

Each comma is an AND /Ruth

[@jonmarquet]

Ruth, queso Cabrales is wonderful, but I prefer queso de Idiazabal, from my Basque Country. Come and try it. !!!!!!, , ,

[@CurbalEN]

+jon marquet Oh, you are Spanish too? Hola! If you ask my dad, cabrales is the best cheese in the world and we all know dads are always right 😁 Jokes aside, I have never tried it. Is it on the northwind dataset too? Is it blue cheese? I will give it a go when I am in Asturias again, but my father's opinions still goes :) /Ruth

[@mohamedchakroun4973]

Thanks you :-) Nice video Could we have a link to this video

[@CurbalEN]

+Mohamed Chakroun Unfortunately I forgot to save it, but if you connect to the northwind data set, you will be able to follow along! /Ruth

[@Bicuriouz]

Really good explanation, thanks !

[@zewditusisay8830]

your videos are good but How can count date of birth and deaths for a given date

[@aahanavikram07]

how can we use the below formula in power query =countif($a$1:a1,a1)

[@CurbalEN]

You do a custom column with an if statement. Post your case in the power bi community to get detailed help. /Ruth

[@micahwolf8123]

Left a big hole for me on how to use sumif when I want it only for the relevant year.

[@charlesadams8922]

Awesome possum!

[@CurbalEN]

+Charles Adams 😂 Thanks! Have a great weekend, Ruth

[@sandip_bettereveryday]

In fact Rob Colie has very rightly defined CALCULATE as "Anything If" function.

[@CurbalEN]

+Sandip Mukherjee I remember when I started using DAX, all my Measures where a form of SUMIF. Nowadays I would say calculate is “anything but if” ;) /Ruth

[@TheresaJones044]

Love your videos however i think it is important that you make them short and straight to the point. You take a while to get there.

[@CurbalEN]

I leave the chit chat at the end on my newest videos. Thanks for the feedback! /Ruth

[@lazarsavic9958]

Countif?

[@CurbalEN]

Watch the video and you will see :) /Ruth

[@judel8535]

Did not show anything dynamic. Just reviewed a basic formula. Nothing more. This will not help if you are looking to understand how to do a dynamic sumif.