Calculated Column

[FrankS72]

I'm struggling to performa required calc, which I want to do in a new column.

Example Data:

Table: Monthly_Sales
Consultant	Month	Sales	Best Month	% Best
Adam	Aug 19	$      1,500	$          1,800	83%
Adam	Sep 19	$      1,200	$          1,800	67%
Adam	Oct 19	$      1,800	$          1,800	100%
Adam	Nov 19	$      1,250	$          1,800	69%
Mandy	Sep 19	$      3,500	$          4,000	88%
Mandy	Oct 19	$      3,250	$          4,000	81%
Mandy	Nov 19	$      4,000	$          4,000	100%
Sue	Nov 19	$      1,150	$          1,150	100%
George	Aug 19	$      1,500	$          1,700	88%
George	Sep 19	$      1,600	$          1,700	94%
George	Oct 19	$      1,700	$          1,700	100%

I want to calculate the 'best month' for each consultant (as a new column), and then compare each month to the best month at a row level.

I'm struggling to perform the best month calculation, as Maxx(Filter(..)) is returning the best month across all consultants, rather than for each.

Help will be greatly appreciated - I'm pretty new to Power BI 🙂

[v-yiruan-msft]

Hi @FrankS72 ,

You can create calculated columns or measures to achieve it:

1. Create calculated columns

Best Month = CALCULATE(MAX('Monthly_Sales'[Sales]),FILTER('Monthly_Sales','Monthly_Sales'[Consultant]=EARLIER('Monthly_Sales'[Consultant])))

% Best = DIVIDE('Monthly_Sales'[Sales],'Monthly_Sales'[Best Month])

2. Create measures

Measure = CALCULATE(MAX('Monthly_Sales'[Sales]),FILTER(ALL('Monthly_Sales'),'Monthly_Sales'[Consultant]=MAX('Monthly_Sales'[Consultant])))

Measure 2 = DIVIDE(MAX('Monthly_Sales'[Sales]),[Measure])

Best Regards

Rena

[amitchandak]

@FrankS72 , try like

divide(sum(Table[sales]),calculate(max(Table[Sales]), allexcept(Table, Table[Consultant])))

Also, refer

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

[andre]

here is a tutorial on how to find best records (which is what you want) and calculate the average of best (which in your case i am not sure what you are trying to do)

you can download the practice materials here: https://businessintelligist.com/2020/07/24/tutorial-how-to-calculate-an-average-of-the-best-using-av...

[Fowmy · Accepted solution]

@FrankS72 

Try This:

 Best NEW = 

VAR BEST = CALCULATE( MAX(SALES[Sales]),ALLEXCEPT(SALES,SALES[Consultant]))

RETURN

BEST

% vs BEST = 

DIVIDE(
    SALES[Sales],
    SALES[Best NEW]
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[FrankS72]

Thank you - this worked well.