Forum Discussion

NehaVageriya's avatar
NehaVageriya
Advocate II
9 years ago
Solved

Last value for column in summary table

Hi,  I have a scenario where I have created a summery of customers, OrderCount,sumofmoneytheyowe,lastpaymentrejecteddate. I have a column as decline reason which is row based and I want to created a...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi NehaVageriya,

     

    The data type of "decline reason" is text, we are not summarize directly. Indirectly, I add a index column in Query Edit. Create a calculte column to get the index according the highest_rejection. Then we can lookup the decline reason based on the index column and calculated column.

     

    MAX = CALCULATE(MAX(Table7[Index]),FILTER(ALLEXCEPT(Table7,Table7[customerId]),Table7[Payment rejection]=MAX(Table7[Payment rejection])))

     

     The following formula returns the declined reason.

     

    Table 3 = DISTINCT(SELECTCOLUMNS(Table7,"5",LOOKUPVALUE(Table7[decline reason ],Table7[Index],Table7[MAX])))



    Finally, combibe other fields, create a new table using the formula below and get expected result.

     

    Table 4 = ADDCOLUMNS(SUMMARIZE(Table7,Table7[customerId],"ordercount",CALCULATE(DISTINCTCOUNT(Table7[orderId]),ALLEXCEPT(Table7,Table7[customerId])),"highest_rejection",CALCULATE(MAX(Table7[Payment rejection]),ALLEXCEPT(Table7,Table7[customerId])),"totalamountown",CALCULATE(SUM(Table7[amount_owe]),FILTER(ALLEXCEPT(Table7,Table7[customerId]),Table7[Payment rejection]=MAX(Table7[Payment rejection]))),"lastrejectiondate",CALCULATE(MAX(Table7[payment_rejection_date]),FILTER(ALLEXCEPT(Table7,Table7[customerId]),Table7[Payment rejection]=MAX(Table7[Payment rejection])))),"declined reason",SELECTCOLUMNS(Table7,"5",LOOKUPVALUE(Table7[decline reason ],Table7[Index],Table7[MAX])))


     

    If you have any issue, please feel free to ask.

     

    Best Regards,
    Angelia

NehaVageriya's avatar
NehaVageriya
Advocate II
9 years ago

Hi, apologies for not posting out clear question. reframing it with a small dataset. 

I have a data where customer, orders are processed to deduct money they owe, and for different reasons payment doesnt go through,which is was my data looks like. below is that data.

 

customerIdorderIdPayment rejectionamount_owedecline reason payment_rejection_date
11120declined_card20/12/2016
12125declined_card20/12/2016
11225expired card21/12/2016
12230expired card21/12/2016

 

What I have done is created a summary table with below

CustomerId,OrderCount,highest_rejections,Total_amount_owe,last_payment_date. what I am looking for is last_decline reason in summary screnario. When I use Lookup or LastNonblank it always gives me both the decline reasons stated for that customerid. Below is how my summary looks 

 

customer_idordercounthighest_rejectiontotalamountowndeclined reasonlastrejectiondate
12255expired card21/12/2016

 

Please let me know if you have any more questions 

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi NehaVageriya,

     

    The data type of "decline reason" is text, we are not summarize directly. Indirectly, I add a index column in Query Edit. Create a calculte column to get the index according the highest_rejection. Then we can lookup the decline reason based on the index column and calculated column.

     

    MAX = CALCULATE(MAX(Table7[Index]),FILTER(ALLEXCEPT(Table7,Table7[customerId]),Table7[Payment rejection]=MAX(Table7[Payment rejection])))

     

     The following formula returns the declined reason.

     

    Table 3 = DISTINCT(SELECTCOLUMNS(Table7,"5",LOOKUPVALUE(Table7[decline reason ],Table7[Index],Table7[MAX])))



    Finally, combibe other fields, create a new table using the formula below and get expected result.

     

    Table 4 = ADDCOLUMNS(SUMMARIZE(Table7,Table7[customerId],"ordercount",CALCULATE(DISTINCTCOUNT(Table7[orderId]),ALLEXCEPT(Table7,Table7[customerId])),"highest_rejection",CALCULATE(MAX(Table7[Payment rejection]),ALLEXCEPT(Table7,Table7[customerId])),"totalamountown",CALCULATE(SUM(Table7[amount_owe]),FILTER(ALLEXCEPT(Table7,Table7[customerId]),Table7[Payment rejection]=MAX(Table7[Payment rejection]))),"lastrejectiondate",CALCULATE(MAX(Table7[payment_rejection_date]),FILTER(ALLEXCEPT(Table7,Table7[customerId]),Table7[Payment rejection]=MAX(Table7[Payment rejection])))),"declined reason",SELECTCOLUMNS(Table7,"5",LOOKUPVALUE(Table7[decline reason ],Table7[Index],Table7[MAX])))


     

    If you have any issue, please feel free to ask.

     

    Best Regards,
    Angelia

    • NehaVageriya's avatar
      NehaVageriya
      Advocate II
      9 years ago

      Hi, 

      Thanks for solution. Unfortunately it didnt work exactly for me this way as. I was getting an error for circular dependency when i was using your DAX. However, you logic of generating an index was very good and I had not known that, using that I solved the issue. And so I am accepting the solution. 

       

      Thanks again