How do I exclude order details from total orders in table?

[ironryan77]

I created the below table visual to display total charges.  The current view is drilled down one layer to the day of the month.  The problem is that I need the total to equal $164,082, not $382,335.  The reason the total is this high is that it is duplicating the charges for the same callkey.  e.g. Call 716533 was only charged $54,171 total, but because this is broken down by day it is tripling this amount. 

These are the DAX formulas I'm using to calculate the above columns. 

0mo charges3 = SUMX(SUMMARIZE(Intra_Month_Chart1_Test,Intra_Month_Chart1_Test[DaysElapsed_Grouped],Intra_Month_Chart1_Test[net_payment_amount],"New Charges",MAX([0mo charges])),[New Charges])

0mo charges4 = SUMX(SUMMARIZE(ALL(Intra_Month_Chart1_Test[DaysElapsed_Grouped],Intra_Month_Chart1_Test[net_payment_amount],Intra_Month_Chart1_Test[callkey]),Intra_Month_Chart1_Test[callkey],"Test",MAX([0mo charges])),[Test])

So what I want is just one column containing the charges, but to display the correct charges per call without these charges being duplicated in the final total row.

[Anonymous · Accepted solution]

Hi @ironryan77,

Based on your description, you want to display the total value of '0mo charges4' measure on '0mo charges3' total row, right?

If this is a case, you can add some condition to filter the total row and use the specified formula to instead the original calculation.

Conditional Measure=
IF(COUNTROW(Intra_Month_Chart1_Test)=COUNTROW(ALL(Intra_Month_Chart1_Test)),
SUMX(SUMMARIZE(ALL(Intra_Month_Chart1_Test[DaysElapsed_Grouped],Intra_Month_Chart1_Test[net_payment_amount],Intra_Month_Chart1_Test[callkey]),Intra_Month_Chart1_Test[callkey],"Test",MAX([0mo charges])),[Test]),//charges4
SUMX(SUMMARIZE(Intra_Month_Chart1_Test,Intra_Month_Chart1_Test[DaysElapsed_Grouped],Intra_Month_Chart1_Test[net_payment_amount],"New Charges",MAX([0mo charges])),[New Charges]))//charges3

Sample:

Modify TotalRow =
IF(COUNTROWS(Sheet2)=COUNTROWS(ALL(Sheet2)),
"Total row formula",
SUM(Sheet2[Amount])) //original formula.

Regards,

Xiaoxin Sheng

[ironryan77]

Awesome!  This works for me with one noteable addition.  I changed the first DAX line of code from

COUNTROW(ALL(Intra_Month_Chart1_Test)),

to

COUNTROW(ALLSELECTED(Intra_Month_Chart1_Test)),