cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 column which gives me lastdecline reason based on lastpayment rejection date. I have tried LastNonBlank, this summarises and gives all decline reason. Also tried lookup but not clear why it stats a loop is created. I cant use more than one columns or measure for this and so looking to get a solution in one DAX . 

Can some one please help me on this, let me know if you have questions.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Last value for column in summary table

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

 

3.png

 The following formula returns the declined reason.

 

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

1.PNG

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


2.PNG

 

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

 

Best Regards,
Angelia

View solution in original post

5 REPLIES 5
Highlighted
Super User IV
Super User IV

Re: Last value for column in summary table

Your question is very hard to understand. Please have a look a this article that describes how to post a question in the forum that makes it easier for the others to answer: https://social.technet.microsoft.com/wiki/contents/articles/28212.how-to-ask-a-power-pivot-question-...

and redesign your question accordingly.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Super User VII
Super User VII

Re: Last value for column in summary table

Also it will be easier to help if you can provide some dummy sample dataset. thanks!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Frequent Visitor

Re: Last value for column in summary table

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 

Highlighted
Microsoft
Microsoft

Re: Last value for column in summary table

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

 

3.png

 The following formula returns the declined reason.

 

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

1.PNG

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


2.PNG

 

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

 

Best Regards,
Angelia

View solution in original post

Highlighted
Frequent Visitor

Re: Last value for column in summary table

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

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors