DAX Force Row summation instead of calculating value at total level

[Anonymous]

Hello,

I'm running into a problem where my DAX formula (Aged Revenue) is being calculated at at the total level instead of summing the individual rows.

I added another measure as a workaround but since my dataset has 5 million records, the visualization fails due to low memory

Appreciate your help in making this faster/efficient.

Here are my measures:

Aged Revenue = 
  if([Backlog Rev]<>[Backlog Rev1],0,[Backlog Rev1])

Aged RevenueX = 

var _new=SUMMARIZE(BacklogDataNew,BacklogDataNew[PID_SO_SS],"_value",[Aged Revenue])

return IF(HASONEVALUE(BacklogDataNew[PID_SO_SS]),[Aged Revenue],SUMX(_new,[_value]))

[Anonymous]

@Greg_DecklerHey Greg. I found your write up on the DAX measure for totals here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

But I'm unable to adapt it to my problem. Could you please help me?

MyMeasure2 = IF(HASONEFILTER(BacklogDataNew[PID_SO_SS]),

IF([Backlog Rev]==[Backlog Rev1],SUMX(BacklogDataNew,[Backlog Rev1]),0),

SUMX(FILTER(BacklogDataNew,[Backlog Rev]==[Backlog Rev1]),[Backlog Rev1]))

[v-kkf-msft]

Hi @Anonymous ,

Has your problem been solved? If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft]

Hi @Anonymous ,

I can't fully understand what you mean, the total value you want to get is 3258909 or 0? If it is 3258909, I tested your formula and found that it gives the correct value. If it is 0, is it any different from the Aged Revenue column?

Best Regards,
Winniz

[amitchandak]

@Anonymous , Simply try like

Sumx(SUMMARIZE(BacklogDataNew,BacklogDataNew[PID_SO_SS],"_value",[Aged Revenue]),[_value])

[Anonymous]

Hello Amit,

Its still running out of resources. I think the problem is with "Aged Revenue" measure. I don't think me using the IF function is helping this cause