Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
NadeemAhamed
Helper V
Helper V

Row wise calculation in percentage

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.

NadeemAhamed_0-1706507388613.png

 

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. 

 

17 REPLIES 17
Daniel29195
Super User
Super User

@NadeemAhamed 

 

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?

@NadeemAhamed 

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

@Daniel29195 ,

 

No Problem. 

Its very great you have seen that image. 

@NadeemAhamed 

 

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! 🤠

@Daniel29195 

 

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.  

 

 

@NadeemAhamed 

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. 

 

@NadeemAhamed 

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 ? 

Daniel29195_0-1706599108980.png

 

 

Daniel29195_1-1706599129409.png

 

 

 

@Daniel29195 

 

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.

NadeemAhamed_0-1706600387876.png

 

In Power BI we have to calculate "Gross Profit Rate" for Plan and Actual columns by using "Gross Profit/Sales"

 

NadeemAhamed_1-1706600626425.png

 

We have to place Gross Profit Rate data values after the Gross Profit row in table vistualization.

 

NadeemAhamed_2-1706600663193.png

 

 

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 : 

Daniel29195_0-1706603166214.png

 

this is the output : 

Daniel29195_1-1706603185850.png

 

 

 

 

 

 

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! 🤠

 

 

 

 

 

@Daniel29195 

 

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. 

 

NadeemAhamed_0-1706616112443.png

 

Could you kindly help me out to resolve this.

@NadeemAhamed 

the formula states : 

grossprofit /  sales 

thus :

9/36 =  25 %

44/10 =  440 %

35 / -26 =  -134%

 

unless the logic is something else

@Daniel29195 

 

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%

@Daniel29195 ,

 

what pbix file you have shared that is contain the old pbix data.

Is it that you have shared?

@Daniel29195 ,

 

Kindly let me know if any clearification and more details required.

 

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)


return
SWITCH(
    TRUE(),
    SELECTEDVALUE(Category[Category]) = "Gross Profit Rate (Gross Profit/Sales)" ,  format(gpr, "##.#%"),
    [variance]
)

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.