Row wise calculation in percentage

[NadeemAhamed]

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. 

[Daniel29195]

@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

[NadeemAhamed]

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?

[Daniel29195]

@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

[NadeemAhamed]

@Daniel29195 ,

No Problem. 

Its very great you have seen that image. 

[Daniel29195]

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

[NadeemAhamed]

@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.  

[Daniel29195]

@NadeemAhamed 

Kindly check the below 

https://drive.google.com/file/d/1wDjBlJuGmvsVFyctYWJH9JuaRNrjoEsk/view?usp=sharing

let me know if this works for you . 

[NadeemAhamed]

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. 

[Daniel29195]

@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 ? 

[NadeemAhamed]

@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.

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.

[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]

@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. 

Could you kindly help me out to resolve this.

[Daniel29195]

@NadeemAhamed 

the formula states : 

grossprofit /  sales 

thus :

9/36 =  25 %

44/10 =  440 %

35 / -26 =  -134%

unless the logic is something else

[NadeemAhamed]

@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%

[NadeemAhamed]

@Daniel29195 ,

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

Is it that you have shared?

[NadeemAhamed]

@Daniel29195 ,

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

[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)

return

SWITCH(

    TRUE(),

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

    [variance]

)