DAX Fridays! #57: Calculate the sum of subcategories

[@jurellrapay9101]

After hours of looking I finally found my answer on this DAX basic. You're still helping people years later! Thank you for this.

[@juliane1mota]

This video was a milestone for me. Thank you ever so much Ruth!

[@CurbalEN]

Awesome to hear :)

[@RumbleLab]

This video was a life saver. Not exactly how I was trying to use it, but got me close enough to figure it out. Thank you!

[@CurbalEN]

Awesome!! /Ruth

[@excelisfun]

Thanks for the DAX Fun!

[@CurbalEN]

+ExcelIsFun Hi Mike, I don’t know how you find the time to comment on my videos, but I just want you to know it means the world to me, big thanks :) /Ruth

[@excelisfun]

: )

[@CurbalEN]

+ExcelIsFun :)

[@ΑντιόπηΠαντελή]

Great, thank you so much! Can you please tell me if we had let's say more parentIDs and we would like to find the (3) with the highest value (distinct ones), what do we use? RANKX?

[@amithababy4745]

You are just amazing!

[@CurbalEN]

Thanks!

[@veeek8]

Great video thank you! For 'earlier' to work does the attribute that is being aggregating against need to be in order, so all like categories are together?

[@colgreg90]

hi Ruth, very handy and helpful video. How can I modify this to get a distinct count of each subcategory instead of SumX

[@ulkaulka9458]

Hi Ruth, thanks for the great video . please make a video on, how to validate data in power bi desktop.

[@akashgawade8157]

Thank you ma'am 🙏 it is really helpful..

[@CurbalEN]

Perfect!

[@ruahlbehr3124]

I hope you could help. I am new to DAX and cant find a video to show me what I am looking for. Do you maybe have a video for the following - If I want to calculate the sum of sales value for each sales person per month in order to get their top 5 months since they started (So top 5 months ever). How do I go about doing that? Will really appreciate your help.

[@maxt.6530]

Is there a way to achieve exactly this result with a measure, when I do not want to include the subcategory in my visual?

[@abhijeetghosh27]

Thank you for this solution.. It helped me with my problem statement. Cheers ! 🥂🥂

[@CurbalEN]

🥳🥳 /Ruth

[@IQ_Update]

Can you do this for hierarchy

[@Victor-ol1lo]

Great video Ruth !! Especailly for people who are new to DAX is really hard to understand the EARLIER (totally ambiguous name) function. I would always prefer CALCULATE + SUM + ALLEXEPT..... Have a nice weekend.

[@CurbalEN]

+Victor Friesen Hi Victor! Thanks! Hope it helps many people too :) Yes, I should try that and explain it also... Happy Friday! /Ruth

[@spacex4496]

I have a query on this, if you calculate the total cost, it will sum the aggregate cost

[@Economia_Bi]

Excelete, justo estaba buscando esta solucion.

[@danilomendes1142]

HI, Ruth How do I find the maximum sales by category

[@ankushjain4128]

waoow learnt one new technique to get subtotals

[@Dragz672]

Does the same syntax work for power query, I.e. M? And if not, do you know how to do it?

[@marcelosanches1578]

Hello, I want to thank you for your videos that help me a lot. I have a question regardingthis great DAX video I am currently working on my , and inside my fact table (where I have all stats) I’m trying to create some calculated columns, in one of this columns I used your DAX code with both (Filter and Earlier) from the video, but my goal is to achieve the sum of players personal fouls per team and per season I want to ask you, if it is possible to do this in DAX code? I sent you an e-mail with more information. Thanks Marcelo Sanches

[@vishalpatil4102]

I am watching this video in 2021, thank you so much

[@CurbalEN]

💃💃

[@Adr_C]

How can you create a measure where you can use the Child(component) ID column but the filter context will return values for the specified ID but also all of it’s children?

[@CurbalEN]

Would you mind posting the question in the pbi community? Travelling at the moment .. /Ruth

[@Adr_C]

Curbal I found the answer here www.daxpatterns.com/parent-child-hierarchies/

[@CurbalEN]

Excellent and thanks for sharing :) /Ruth

[@ankurkhaparde6641]

nice explanation!

[@dontblink9065]

Great solution for sum by category, Thanks

[@CurbalEN]

Thanks! ...and I wont! Blink that is... 😂😂😂 /Ruth

[@srb1303]

Hi, I tried to sum for specific contain word from specific field, the contain such as 'AYW' or 'AYW1234' or '1234AYW', can you help me out

[@sadadeepsingh4096]

what would be your approach if the path length was in a different sheet and corresponded to this by another column ?

[@CurbalEN]

Not sure what you mean. For support questions, could you please post in the Power bi Community? Thanks! /Ruth

[@marzenawitek1628]

Hi, I tried using the same logic to calculate total time spent on tasks per team member. However, in a calculated column it returns the same values instead of sum of time spent. Is it because value type in column 'Time Spent' is set as 'Duration'?

[@CurbalEN]

If your Dax calculation is returning the same value on each cell, it means that something is being filtered incorrectly. Check my 2 DAX 101 videos to see if you can find out what your problem is: m.kzbin.info/aero/PLDz00l_jz6zwdC_xdTp_QANkHYIzs1BJG /Ruth

[@coolblue5929]

Thanks for the video. I don't understand the logic for this. My understanding is that, if you are filtering to select only rows where the ParentID is the same as the previous row, then you should miss the first one and the result for the first one should be 14, not 16. What am I missing here?

[@28abs]

have the same query, any leads on this?

[@hello-u2w]

how do I do this in a measure??

[@jakael02]

Thanks Ruth!

[@CurbalEN]

You welcome and have a nice weekend :) /Ruth

[@ricsinagy3465]

I've tried this in 2021 and unfortunately it's not working. Could you please tell me what could've changed? Many thanks!

[@ashishchanchalverma5722]

This was very tough DAX measure

[@mauriciopriego1700]

thank you so much.

[@JesusCastro-fo6sn]

Hola, Ruth. ¿Puedes hacer algún tutorial en español? Creo que varios de tus seguidores estaríamos muy agradecidos. Saludos desde Saltillo, Mexico.

[@CurbalEN]

+Jesus Castro Hola Jesus! Si por supuesto. Ya va siendo hora verdad? /Ruth

[@sahiljainnit]

table name is not auto-coming after the earlier parenthesis, hence i am unable to solve it

[@devi.elvira]

Why not working on me?

[@muhammadmuavia8024]

how to add cost values of same products in same column Options 3 hours ago Rep office status cost A2 processing 1902 A2 processing 8721 A9 overdue 1921 A2 processed 2001 A2 processed 2213 A9 processed 22113 A5 processing 2213 A2 processing 4343 A5 Overdue 2321 A2 closed 1123 A5 closed 9382 A5 closed 8382 A5 processing 912 i have a data like this and i want to calculate same name values from status column and than add the cost of that all values for example rep office of A2 have a processing status of 3 times and i need to add that all3 processing cost 1902+8721+4343 than A2 have a status of processed 2 times taht cost 2001+2213=4214and my end result or graph should be like this Rep office closed overdue processed processing A2 1123 - 4214 14966 A5 A9 .. which query or expression i need to enter thanks

[@CurbalEN]

Hi. Can you post in the power bi community? Thanks! /Ruth

[@varaprasadk]

Hi it would be appreciated if you can guide me to get this in new measure calculate filter as multiple columns included IIF(SELECTEDVALUE(TimePeriod[Period_Code]) = 'WTD', 'cv_exp_data_store_sum[CURR_FW_FLAG] = 1', IIF(SELECTEDVALUE(TimePeriod[Period_Code]) = 'LFW', '[Fiscal/Calendar.LASTFW] = 1', IIF(SELECTEDVALUE(TimePeriod[Period_Code]) = 'MTD', '[Fiscal/Calendar.CURR_FM_FLAG] = 1', IIF(SELECTEDVALUE(TimePeriod[Period_Code]) = 'STD', '[Fiscal/Calendar.CURR_FS_FLAG] = 1', IIF(SELECTEDVALUE(TimePeriod[Period_Code]) = 'YTD', '[Fiscal/Calendar.CURR_FY_FLAG] = 1',

[@frankgovers8420]

Sorry Ruth, but this is a bit chaotic and not one of your best videos. And columns are not a great solution Not the best example. I am always straight, When its good, I will tell it, but also when it is not so good. Sorry

[@CurbalEN]

+Frank Govers Ahhh, sorry to hear that, but thanks for the feedback! Happy Friday :) /Ruth

[@bsrp4059]

You have taken so confusing path items when you can get many intuitive examples of data are available. Please do not take in future. Otherwise you tried to convey a good technique..cheers from india.

[@CurbalEN]

+B Srp Thanks for the feedback and have a nice day :) /Ruth

[@stephanweaver1960]

Couldn't follow, sorry

[@aaroncouto5806]

not the most efficient way to do this, but good try.

[@CurbalEN]

Thank you :) /Ruth