reference text
1 Topic
SUMIFs in DAX to add a factor of another row to the current row
Hi, I am trying to build a DAX formula to build a measure in Power Pivot to replicate what I am doing in a table formula right now in Excel. Goal is increase speed, the data set is quite massive and the table formula is very slow. I have a data set where there is a parent producer and 0-2 children producers that I would like to combine production from. I want to add half of each adjacent child producer to the parent producer volume. So, no adjacent children, parent production remains the same; 1 child, parent production + 0.5*child production; 2 children, parent production + 0.5*child_1 production + 0.5*child_2 production. I made a table to define how many children a parent has, 0 means none, 1 means a child with the same number as the parent, -1 means a child number less than the parent and 2 means there is a child on either side. For example, assume the following producer arrangement: P1, P1C, P2, P2C, P3, P4 Table name: ParCh Parent Child P1 1 P2 2 P3 -1 P4 0 Dataset looks like this, each producer gets one line per day. In actuality there are multiple columns that would all need to be combined in this fashion, but they are all the same idea, so this is representative Daytime Producer Production 2020/04/01 P1 100 2020/04/01 P1C 50 2020/04/01 P2 125 2020/04/01 P2C 80 2020/04/01 P3 200 2020/04/01 P4 175 Using the following cell formula I am able to get my desired result. Note the IF statements for D1 are teh only case where the parent prefix does not match the child prefix. =IFERROR(IF(INDEX(ParCh,MATCH([@Producer],ParCh[Parent],0),2)=2,[PRODUCTION]+0.5*(SUMIFS([PRODUCTION],[DAYTIME],"=" &[@DAYTIME],[Producer],"=" &[@Producer] & "C")+SUMIFS([PRODUCTION],[DAYTIME],"=" &[@DAYTIME],[Producer],IF([@Producer]="D1","=C0C","=" & LEFT([@Producer],1) & MID([@Producer],2,1)-1 & "C"))),IF(INDEX(ParCh,MATCH([@Producer],ParCh[Parent],0),2)=1,[PRODUCTION]+0.5*SUMIFS([PRODUCTION],[DAYTIME],"=" &[@DAYTIME],[Producer],"=" &[@Producer] & "C"),[PRODUCTION]+0.5*SUMIFS([PRODUCTION],[DAYTIME],"=" &[@DAYTIME],[Producer],IF([@Producer]="D1","=C0C","=" & LEFT([@Producer],1) & MID([@Producer],2,1)-1 & "C")))),IF(RIGHT([@Producer],2)="C","",[PRODUCTION])) Daytime Producer Production Parent + 0.5Child 2020/04/01 P1 100 125 2020/04/01 P1C 50 2020/04/01 P2 125 190 2020/04/01 P2C 80 2020/04/01 P3 200 240 2020/04/01 P4 175 175 This is quite a cumbersome formula to run on a large dataset, and is taking way too long to execute for it to be of use. I have found that measures execute much fast I am just unsure how to get this result using a DAX formula. I have tried using the Calculate function to filter data, but I am unable to make a reference to the producer designation to do so. I'm not sure if this is possible in DAX, but if anyone has any tips or solutions, they would be greatly appreciated.Solved1.5KViews0likes2Comments