Forum Discussion
Helper VRow wise calculation in percentage
Helper VDear 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.
Community Championthese 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 ?
- Daniel29195
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! 🤠 - NadeemAhamed
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.
- NadeemAhamed
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.
- Daniel29195
the formula states :
grossprofit / sales
thus :
9/36 = 25 %
44/10 = 440 %
35 / -26 = -134%
unless the logic is something else
- NadeemAhamed
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%
- NadeemAhamed
what pbix file you have shared that is contain the old pbix data.
Is it that you have shared?
- NadeemAhamed
- NadeemAhamed
Dear Daniel29195 ,
I tried this below DAX for above requirement. but bad luck.
Could you please help me out to achieve this
test_Variance data =var p_sales =CALCULATE(SUM('P&L Report'[Plan]),Category[Category] = "Sales",REMOVEFILTERS(Category[Order]))var p_grossprofit =CALCULATE(SUM('P&L Report'[Plan]),Category[Category] = "Gross Profit",REMOVEFILTERS(Category[Order]))var p_Grossprofitrate=DIVIDE(p_grossprofit,p_sales,0)var a_sales=CALCULATE(SUM('P&L Report'[Actual]),Category[Category] = "Sales",REMOVEFILTERS(Category[Order]))var a_grossprofit=CALCULATE(SUM('P&L Report'[Actual]),Category[Category] = "Gross Profit",REMOVEFILTERS(Category[Order]))var a_Grossprofitrate =DIVIDE(a_grossprofit,a_sales,0)var gpr=CALCULATE( a_grossprofit-p_grossprofit)returnSWITCH(TRUE(),SELECTEDVALUE(Category[Category]) = "Gross Profit Rate (Gross Profit/Sales)" , format(gpr, "##.#%"),[variance])