Calculated Column for Top 3 Spenders Month by Month based on BU and Total Spend

[kalkhudary]

Hi Everyone, 

I have a table like this in my dashboard, I want to create a calculated column that writes Top Spender based on Total Amount Spent per person in a each month seperately for people who work in the same business and based on the top 3 spenders amounts, which ca yield the result below like in the last column. 

Appreciate any help on this!

Name	Spend	Business	Date
Max	100	Marketing	24-Jan
Zak	20	Accounting	24-Jan
Sophia	90	Marketing	24-Jan
Hana	40	Accounting	24-Jan
Mark	70	Marketing	24-Jan
Zak	10	Accounting	24-Jan
Max	55	Marketing	24-Jan
Sophia	25	Accounting	24-Jan
Philip	5	Marketing	Jan-24
Zak	60	Accounting	23-Dec
Mark	30	Marketing	23-Dec
Hana	80	Accounting	23-Dec
Mark	30	Marketing	23-Dec
Zak	50	Accounting	23-Dec
Mark	15	Marketing	23-Dec
Hana	50	Accounting	23-Dec
Max	10	Marketing	23-Dec
Zak	40	Accounting	23-Dec
Max	5	Marketing	23-Dec
Hana	200	Accounting	23-Dec

This result table would look something like the below 

Click Marketing Slicer

Click January in Date Slicer 

Name	Spend	Business	Top Spender flag	Top Spender_Jan	Top Spender Repeats from past 2 months
Max	155	Marketing	Top Spender	1	2
Mark	70	Marketing	Top Spender	1	2
Sophia	90	Marketing	Top Spender	1	1

[kalkhudary]

@Ashish_Mathur I think the top spender flag is the same as the Jan_Top Spender. Probably we can drop it and Use Jan_Top Spender, Dec_Top Spender and then the final Column which is the sum of occurrences for Jan and Dec nevertheless the date slicer is set for Jan for example. Any thoughts on how we can DAX the intended result.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[kalkhudary]

@Ashish_Mathur I am super thankful for your help on this, really so happy. However, I have tested the measure of occurences as top spender and doesn't seem to perform the intention. If you changed in the file the "measure" to Top 1 to zoom more on the case, you see that the measure is giving you the number of time the person showed in the file, not the number of times where a person showed as a top spender whether top 3 or top 1.

Top 3 spenders = CALCULATE([S],TOPN(1,ALL(Data[Name]),[S],DESC),VALUES(Data[Name]))

See below example, Mark showed as Top Spender in December. In January, Max did not show as top spender in the previous month, so the value should be 0 but shows as 2 here as it is counting how many times Max overall showed in the spenders list whether he is top spender or not.

My intention is to show how many times a person did occur as a top spender based on previous 3 months for example. Anything we can adjust in the Dax to make it work.

Thanks Ashish.

[Ashish_Mathur]

Hi,

Can you live with this?  The 2 measures will tell you what the rank was in the current and in the previous month.

Hope this helps.

[kalkhudary]

@Ashish_Mathur I wish this would solve it. I need to find a Dax that basically count the times based on past 3 months where an employee from the top 3 spenders showed as a top spender. If John showed as Top Spender in Jan and was a top spnder in Dec and November,the value should be 3 and if he showed only in Jan, the value should be 1 - taking into consideration that we filtered date to Jan.

I am trying to find a method to dax it, not successful so far, trying with dateadd function now.

[Ashish_Mathur]

Hi,

Try this file.  The measure checks in the selected month and in 1 month prior.

[kalkhudary]

@Ashish_Mathur Thank you so much that seems to work for previous month. Quick question, when I applied the measure to my data, It seems it is giving me the counts not only based on top 3 but also looking into the other employees who are on the list and count them as part. Any idea what to include in the measure to restrict it only to look at top 20 from previous month. Any function tha can help do that. 

[Ashish_Mathur · Accepted solution]

That should not be happening.  I do not know the mistake you are commting.  Based on the data you shared with me, the file which i shared in my previous message does the job.

[kalkhudary]

@Ashish_Mathur Thanks for helping me break my thoughts, I have included the table with expected result table that I would need to show. One for Top Spender flag, one for the occurrence during the chosen month , and one for the ocurrence of this person in top spender list from previous month.

Hope that clears it more. How can we Dax the 3 critria, I am struggling since 2 days to fnd a proper and neat solution.

[Ashish_Mathur]

What do the 1's in the "Top Spender_Jan" column signify?  Why is ths column needed when you already have the "Top Spender flag" column?

[kalkhudary]

@Ashish_Mathur My final intention is to create a measure that would count instances of top spender based on past 3 months. So if Max showed in Jan, Dec, and November as Top Spender, then he has 3 instances of repeatitive Top Spender. Any thoughts how to reach this outcome. This is the only way that came to my mind so a calculated column would help reach this outcome.

[Ashish_Mathur]

Hi,

Based on the table that you shared in the first post, show the expected result, with an explanation.  Assume that there is no "Top 3 Spenders Month by Month based on BU and Total Spend" column in your dataset.

[Ashish_Mathur]

Hi,

Why do you want to create a calculated column formula?  Why not a measure?  See this article - Vendor Analysis In Inventory Management Dashboards | Master Data Skills + AI (enterprisedna.co)