DAX Fridays! #91: Sum child values into the parent

[@andriskriekis6486]

Hi Ruth, Think I have the solution just without SUMX ann FILTER (see formula below): Shipping costs parent = IF(Orders[Related] = 1; CALCULATE( SUM(Orders[Child Shipping costs]); ALLEXCEPT(Orders;Orders[OrderId]) ) )

[@CurbalEN]

Ahh! Much better! You are a ⭐️ Andris!! Pinned for more visibility! /Ruth

[@andriskriekis6486]

Thanks for the Star

[@CurbalEN]

😊

[@jerrykappa6899]

Great... I am using this approach quite often.. nevertheless we could use as well a calculatetable with a summarize function. If the data set is huge, it is faster than related. I tend to work with big data set ie between 60 and 100 millions rows therefore calculatetable with summarize is more efficient for me... when possible. In your example you used sumx. I do calculate function with sum and filter as presented and it works the same way avoiding the sumx which is more resource hungry... good job on the videos... waiting for the next ones...

[@CurbalEN]

Oh thanks! I should probably do a video on that :) /Ruth

[@powershah]

Curbal I will be waiting for that video.

[@victorarayal]

Hola Ruth! Buen video, gracias! Consulta. Que herramientas usas para escribir y destacar sobre la pantalla?

[@CurbalEN]

El surface book 2 (o un ordenador con pantalla táctil), el lápiz del surface y un programa que es gratis, no recuerdo el nombre (lo miro el lunes!) /Ruth

[@rahulgupta35]

How can I use it in a multilevel Hierarchy ?

[@diogobueno176]

Hello, I'm struggling with that but in dax, can you help me sying some whats I can do it ? Ou how can I search to understand betternabout the sum of values to parent

[@NaraMeerammaTrust]

Ruth, I don't find this video on download center now. Can you please upload it if possible. I have similar situation on my current project. I wanted to see and practice before come up with appropriate solution. Please upload it at our earliest possible convenience. Thank you

[@CurbalEN]

If it is not there, i am afraid I dont have it. All my files are stored in the download center. :( /Ruth

[@prateekraina2781]

Nice video. In my experience EARLIER function should be avoided incase the dataset is huge. It performs very slow. What’s your take on that?

[@CurbalEN]

Hi! How a measure or calc column performs depends on so many factors: size of the tables (wide and hight) , how the model looks like, type of connection to the source... Difficult to generalize... /Ruth

[@myexperiments7298]

Hi, I have two dimension tables (both with parent chil hierarchies with more than 6 levels). And one single fact table with only the child members and their corresponding sales values. I want to display a matrix with the sum of sales when a parent or a child is selected from the dimension tables. I tried using the path function, but this gives correct value only when one dimension is selected. When values are selected from the other dimension slicer, the values are not getting affected by the selection. Please help me with a solution!

[@MartinMunoa]

Hola Ruth! Se te ocurre qué se puede hacer cuando hay mas de un nivel de jerarquías? En mi caso hay unos 4 niveles, y necesito que primero se calcule el subtotal del último nivel antes de calcular el siguiente porque sino no hay de donde obtener el valor del costo del nivel anterior.. (En este caso del video funciona porque todos los elementos del nivel anterior - los "child" - tienen un valor ya definido por dato). Gracias!

[@vishaljhaveri7565]

Thank You! Nice explanation.

[@tanababa]

Thank you Ruth. Great video, Tamir

[@CurbalEN]

Thanks for the feedback! :) /Ruth

[@aks541]

One query- this if(Order[related] = 2,... part of the formula is required to display the calculated filed "Shipping Cost Parent" is nice fashion otherwise it will look clumsy. Am I correct?

[@CurbalEN]

Mmmm not sure what you mean? /Ruth

[@aks541]

Curbal I was talking about the formula that you had used for summation of parent fields

[@CurbalEN]

I got that, but what is your question?

[@aks541]

My observation is the part of the first DAX measure i.e. if(Order[related] = 2 has only the significance of displaying the total for those rows where Related value =2. So it's use to increase the readability not for any other reason. Am I correct here? Or I'm missing something

[@CurbalEN]

Pulling from memory, that was to do the calculation on the child and not the parent. /Ruth

[@lukaszrutkowski]

Hi Ruth, I have a larger structure than child to one parent. The parent can have parent and so on. Let's say there are 5 levels. For each level I would like to have the sum for all the children below going to the leaf of the tree. So the most top parent would have the sum of everything. What solution would you recommend? PS. Option2: the number of levels is unlimited.

[@gemmaparker82]

This is the exact problem I am also trying to solve at the moment! Did you find a solution?

[@lukaszrutkowski]

@@gemmaparker82 I have decided to use SQL as I feel more comfortable in it. I have created recursive function that generates the levels as separate columns. Then in DAX I am using SUMMARIZECOLUMNS to get final result, but this approach is good is for the fixed number of levels. I think I would be able to create a recursive SQL statement for unlimited number of levels, but when it comes to measures in DAX I think you have to always do it one by one manually (not sure, I am not good in DAX). I think if you want only DAX approach you should use PATH and the other path-related commands. Let me know please if you find something better :)

[@eladiobardelli3001]

Lo que explicaste se puede usar para hacer informes de balances , asumiendo el id como las agrupación de las cuentas activo,pasivo,perdidas y ganancias y obtener un balance automático cierto Ruth , puede ser una buen uso de esa manera , gracias saludos desde Chile.

[@CurbalEN]

Ahh pues si! Excelente y gracias por compartir! /Ruth

[@rrrprogram8667]

Nice video ruth

[@CurbalEN]

Thanks and have a great weekend :) /Ruth

[@NaraMeerammaTrust]

I see. That's OK Ruth. I will manage it. Thanks for your time

[@CurbalEN]

Sorry about that :) /Ruth

[@mehdihammadi6145]

Thank you for sharing

[@CurbalEN]

You welcome. :) /Ruth

[@frankgovers8420]

👍👍

[@CurbalEN]

:D /Ruth

[@rassten]

it can not be right, it seems too easy :-)

[@CurbalEN]

It might wrong so beware ;) /Ruth