total by year and ranking based on specific year

[huynhphuc242]

Hi everyone, 

I need help with a DAX for a calculated column for "total per year" and "Tier based on 2018 revenue" like below: 

customer	billed date	Year billed	revenue	Total per year	Tier based on 2018 revenue
A	1/1/2018	2018	$300	$1,800	1
A	2/1/2018	2018	$300	$1,800	1
A	3/1/2018	2018	$300	$1,800	1
A	4/1/2018	2018	$300	$1,800	1
A	5/1/2018	2018	$300	$1,800	1
A	6/1/2018	2018	$300	$1,800	1
A	1/1/2019	2019	$1,000	$6,000	1
A	2/1/2019	2019	$1,000	$6,000	1
A	3/1/2019	2019	$1,000	$6,000	1
A	4/1/2019	2019	$1,000	$6,000	1
A	5/1/2019	2019	$1,000	$6,000	1
A	6/1/2019	2019	$1,000	$6,000	1
B	1/1/2018	2018	$5,000	$11,000	3
B	2/1/2018	2018	$6,000	$11,000	3
B	12/1/2019	2019	$70,000	$150,000	3
B	12/1/2019	2019	$80,000	$150,000	3

I need this total per year to rank customers into Tier based on their revenue in the year of 2018. So the Tier column has this logic: 

Tier	2018 revenue logic
1	<2,000
2	<4,000
3	<15,000
4	<200,000

So even though customer B has 150,000 revenue in 2019 (tier 4), their Tier in 2018 is 3 so they will still be in Tier 3. 

Much appreciated all your help! 

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i have two ways to meet your requirement:

By Calculated Column:

1)Create a column regardless of year:

Column = SWITCH(true,[Total per year]<2000,1,[Total per year]>2000&&[Total per year]<4000,2,[Total per year]>4000&&[Total per year]<15000,3,[Total per year]>15000,4)

 2)Create a column to filter year:

rank-column = IF('Table'[Column]<>CALCULATE(MIN('Table'[Column]),ALLEXCEPT('Table','Table'[customer])),CALCULATE(MIN('Table'[Column]),ALLEXCEPT('Table','Table'[customer])),'Table'[Column])

And it shows:

By Measure:

1)Create a measure to change the revenue to the same group by customer:

Measure = CALCULATE(SUM('Table'[revenue]),FILTER(ALLSELECTED('Table'),'Table'[customer] in FILTERS('Table'[customer]) && YEAR('Table'[billed date]) = 2018))

2)Create a measure to rank the measure above:

rank-measure = IF([Measure]<2000,1,IF([Measure]<4000,2,IF([Measure]<15000,3,IF([Measure]<200000,4))))

And it shows:

Here is my test pbix file.

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[huynhphuc242]

thank you for your help @v-gizhi-msft 

[amitchandak]

Try the following as columns

Total 2018 year =sumx(filter(table,table[customer]=earlier[table[customer] && table[Year billed] =2018),	table[revenue])

Tier = if(table[Total 2018 year] <20000," <2,000",....)

If first is showing 2018 against all rows, then you can use it if or case to develop tier.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[huynhphuc242]

Hi @amitchandak , thank you for your advice, I tried create a calculated column as you suggest but got this error saying too few arguments were passed to the sumx fuction.

I tried this: Total 2018 year = sumx(filter(table,table[customer]=EARLIER(table[customer] && table[year_billed]=2018), table[revenue]))

[Ashish_Mathur]

Hi,

Why do you want to solve this with a calculated column formula?  Why not solve it with a measure?

[huynhphuc242]

Hi @Ashish_Mathur , I want to calculate the total 2018 as a calculated column so the Tier can be based off this column. With measure, it does not work as I add more filter breakdown to the table. My goal is to see the revenue retention from customers in cohort 2018.