Forum Discussion
Calculated Column
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 🙂
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 🙂
5 Replies
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 🙂
Thank you - this worked well.
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-averagex-and-addcolumns-functions/
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
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
