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.  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.

 

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

2 Replies

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