Forum Discussion

ironryan77's avatar
ironryan77
Icon for Kudo Commander rankKudo Commander
9 years ago
Solved

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

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. ...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
ironryan77
Icon for Kudo Commander rankKudo Commander
9 years ago

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)),