Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Dear Community.
Good Day.
I have posted this before, unfortunately i didn't get the answers.
Requesting all to help me out.
I have one P&L report. in this i have received only Plan and Actual values for category for multuple divisions.
I have attached the Pbix for your reference.
https://toyotsu.box.com/s/mboowbzdtokv925yklhig6t8dd74uli5
I got DAX for calucating the Variance and Achievement columns.
I requesting help from you guys to calculate Gross Profit Rate (Gross Profit/Sales) for perticular division in row wise and place the value in perticular column in vistualization like as shown above table.
Its realy great thanks to all in advance.
Hello Nadeem,
can you show an example of the desired result , ( in excel per example ) .
cause im not sure i quite understand the problem with calculating (Gross Profit/Sales) since you already calculated the Variance and Achievement column
best regards
Dear @Daniel29195 Sir,
Good Day.
Thank you for your valuable time to read and reply my post.
As i understood your question u want to see the example how i am calculating the difference in excel sheet, Is It right?
nevermind, my bad, didnt focus on the image you have attached.
i got what you want 😅
give me some time and i will send you the solution .
best regards
link:
https://drive.google.com/file/d/1gxq7LrYwLHp0_o9owqj3g3mVO9GNAyza/view?usp=sharing
kindly check the above link and download the solution.
summary :
creating measure which check if category = " gross profi rate" , it uses format function to format the value as % ) .
let me know if this works for you .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅!
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
Thank your suggestion.
Requirement:
From sales dept i will receive only Plan and Actual values for category and division wise.
1. I have to calculate Variance(Actual - Plan) and Achievement (Actual/Plan) Values in seprate column, that is resolved.
2. I have calculate "Gross Profit Rate" value from "Gross Profit/Sales" and have to place after Gross Profit Row to pertcular Plan, Actual, Variance and Achievement Columns in percentage.
3. When we are selecting multiple division "Gross Profit Rate" value should not get sum, it should be take the current "Sales and Gross Profit" values for calucation.
Kindly check the below
https://drive.google.com/file/d/1wDjBlJuGmvsVFyctYWJH9JuaRNrjoEsk/view?usp=sharing
let me know if this works for you .
Dear @Daniel29195
Sorry for saying this,
As per the pbix i think you have taken the values from the table it self.
Where i want to calculate the values in Power BI. as from the sale person we are not going to get that values.
these are the columns im using.
these are the only columns that my calculations are based on .
eventhough in the one i shared i didnt delete the other 2 columns (variance - actual and achievements ) but i didnt use them in my calculations,
so my calculations are base on only : plan and actual.
am i missing something ?
Thank your valuable time to response.
Yes sir, its realy great you are using only two column such as Plan and Actual column.
But my query is from the sales dept i will get only this table data.
In Power BI we have to calculate "Gross Profit Rate" for Plan and Actual columns by using "Gross Profit/Sales"
We have to place Gross Profit Rate data values after the Gross Profit row in table vistualization.
Hello @NadeemAhamed
kindly check now : filename : Calucating Variance, Achievement from Plan and Actual1
https://drive.google.com/file/d/1Fh9SEHvOatffQFsUoCBmVciNK7NCk9jw/view?usp=sharing
this is the data used :
this is the output :
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅!
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
Thank you soo much for your solution,
But i found some calucation is wrong.
Gross Profit Rate is getting wrong calculation in all columns (Plan,Actual, Variance & Achievement). Other Values are coming correct.
Could you kindly help me out to resolve this.
the formula states :
grossprofit / sales
thus :
9/36 = 25 %
44/10 = 440 %
35 / -26 = -134%
unless the logic is something else
For Plan and Actual column data is correct- Gross Profit Rate = Gross Profit/Sales.
Variance and Acheivement column for "Gross Profit Rate" values should be like other values in the column .
Variance = 440.0% - 25.00%=415%
Achievement = 440.0% / 25.00% = 17.6%
what pbix file you have shared that is contain the old pbix data.
Is it that you have shared?
Dear @Daniel29195 ,
I tried this below DAX for above requirement. but bad luck.
Could you please help me out to achieve this
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
65 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |