Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 🙂
Solved! Go to Solution.
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
@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
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...
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you - this worked well.
User | Count |
---|---|
91 | |
77 | |
71 | |
65 | |
58 |
User | Count |
---|---|
103 | |
94 | |
75 | |
61 | |
58 |