Calculting Gross Profit Rate, Variance and Achievement for P&L Report

[NadeemAhamed]

Dear All, 

Good Day. 

Thank you for your valuable time to read and helping to achieve below points. 

Iam creating Power BI Report for P&L Report. 

I have table data in excel as shown in below.

I have attached the Pbix for your reference.

https://toyotsu.box.com/s/mboowbzdtokv925yklhig6t8dd74uli5

Requirement 1: 

Actually Gross Profit Rate Row data, variance and Achievement column data will not be provided by the sales team.

I can calucate Variance and Achievement data.

I need to find the way how to calculate Gross Profit Rate (Gross Profit row value/ Sales row value) based on Divsion and show it in for the perticular rows.

Requirement 2:- 

When i have fetch the excel data to Power BI the Category order is getting change to Asc or Decs order. 

But i need to show as it is in the excel.

I tried to create new table for the Category with Order column and Shorted by Order Number, But i am getting first category name as blank. 

Requirement 3:- 

When we want to show all data for Multiple Divsions.

Plan and Actual values are getting sum.

Variance,Achievement and Gross Profit (Which are calucated values) are also getting sum.

But actually Variance, Achievement and Gross Profit values are should take from the sum of Plan and sum of Actual values when we select Multiple Divisions.

[v-junyant-msft]

Hi @NadeemAhamed ,

First I need to correct an error inside your dataset:

It should be 0 here.

Regarding your first question, since the data you need to participate in the calculation "Gross Profit row value/Sales row value" is in rows instead of columns, you can't calculate it directly, I can put it into columns by transposing it in Power Query, but it will destroy the original data structure, and these two columns will be transposed to rows as well, which is impossible to use:

For your second question, the reason it doesn't show up is that the names don't match. The name in your original table is Gross Profit Rate (Gross Profit/Sales) and the name in your manually created table is Gross Profit Rate. change it to match and it will display:

For your third question, I'll use "Sum of Achievment" as an example, you can use the following DAX to create a measure and put this measure into table visual:

Sum of Achievment = 
VAR A = 
CALCULATE(
    SUM('P&L Report (copy)'[Actual]),
    ALL('P&L Report (copy)'),
    'P&L Report (copy)'[Division] IN VALUES('P&L Report (copy)'[Division])
)
VAR B = 
CALCULATE(
    SUM('P&L Report (copy)'[Plan]),
    ALL('P&L Report (copy)'),
    'P&L Report (copy)'[Division] IN VALUES('P&L Report (copy)'[Division])
)
VAR C =
CALCULATE(
    SUM('P&L Report (copy)'[Actual]),
    ALL('P&L Report (copy)')
)
VAR D = 
CALCULATE(
    SUM('P&L Report (copy)'[Plan]),
    ALL('P&L Report (copy)')
)
RETURN
IF(
    ISFILTERED(Category[Category]),
    DIVIDE(SUM('P&L Report (copy)'[Actual]), SUM('P&L Report (copy)'[Plan])),
    IF(
        ISFILTERED('P&L Report (copy)'[Division]),
        DIVIDE(A , B),
        DIVIDE(C , D)
    )
)

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NadeemAhamed]

Dear @v-junyant-msft 

Thank you for your valuable time to read and helping me to resolve this issue. 

Could you please help me out how to apply formate for variance column values as percentage only where category="Gross Profit rate (Gross Profit/Sales)" with 2 digit number.

[v-junyant-msft]

Hi @NadeemAhamed ,

If you want the results to be formatted as a percentage, you can click here:

If you want to set the number of decimal places to 2, change it here:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NadeemAhamed]

@v-junyant-msft 

As per the your result i have created variance column in that i have to show all column value data in numbers for all category. but where category ="Gross Profit Rate (Gross Profit/Sales) " the value should be in percentage. 

[v-junyant-msft]

Hi @NadeemAhamed ,

Sorry for misunderstanding!
Please change the DAX into this:

Sum of Achievment = 
VAR A = 
CALCULATE(
    SUM('P&L Report (copy)'[Actual]),
    ALL('P&L Report (copy)'),
    'P&L Report (copy)'[Division] IN VALUES('P&L Report (copy)'[Division])
)
VAR B = 
CALCULATE(
    SUM('P&L Report (copy)'[Plan]),
    ALL('P&L Report (copy)'),
    'P&L Report (copy)'[Division] IN VALUES('P&L Report (copy)'[Division])
)
VAR C =
CALCULATE(
    SUM('P&L Report (copy)'[Actual]),
    ALL('P&L Report (copy)')
)
VAR D = 
CALCULATE(
    SUM('P&L Report (copy)'[Plan]),
    ALL('P&L Report (copy)')
)
VAR E =
IF(
    ISFILTERED(Category[Category]),
    DIVIDE(SUM('P&L Report (copy)'[Actual]), SUM('P&L Report (copy)'[Plan])),
    IF(
        ISFILTERED('P&L Report (copy)'[Division]),
        DIVIDE(A , B),
        DIVIDE(C , D)
    )
)
RETURN
IF(
    SELECTEDVALUE(Category[Category]) = "Gross Profit Rate (Gross Profit/Sales)",
    FORMAT( E, "Percent"),
    E
)

And set the number of decimal places to 2:

The final output is below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NadeemAhamed]

@v-junyant-msft 

Good Day.

Thank your for your valuable time to understand and help. 

I have found the solution for 1st requirement. 

I have attached the pbix file for your reference. 

https://toyotsu.box.com/s/5ohfmo6nu51lj4rtp4oxq1csms4vndfc

Now i have to simply calucate Variance (Actual-Plan) and Achievement (Actual/Plan) column values from Actual and Plan column.

Other values are coming correct as per the calcualtion but for the Gross Profit Rate (Gross Profit/Sales) row values coming wrong for the variance and achievement column. 

1. Actual and Plan column: - Gross Profit Rate = Gross Profit row value/Sales row value

2. Variance column: - Actual - Plan  

3. Achievement column: - Actual/Plan.

[v-junyant-msft · Accepted solution]

Hi @NadeemAhamed ,

I looked at your pbix file and in the process of calculating Variance data for Gross Profit Rate (Gross Profit/Sales) he would perform the following steps in the calculation if the calculation is done using the method you have chosen:

（42.00-6.00） / （6.00-30.00）= -150%

I create a new measure:

Measure = 
VAR A = 
CALCULATE(
    SUM('P&L Report'[Actual]),
    'P&L Report'[Category] = "Gross Profit",
    ALLSELECTED('P&L Report')
)
VAR B = 
CALCULATE(
    SUM('P&L Report'[Actual]),
    'P&L Report'[Category] = "Sales",
    ALLSELECTED('P&L Report')
)
VAR C = 
CALCULATE(
    SUM('P&L Report'[Plan]),
    'P&L Report'[Category] = "Gross Profit",
    ALLSELECTED('P&L Report')
)
VAR D = 
CALCULATE(
    SUM('P&L Report'[Plan]),
    'P&L Report'[Category] = "Sales",
    ALLSELECTED('P&L Report')
)
RETURN
(A / B) - (C / D)

And change the Variance data into this:

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

The final output is below:

And about this one:

700% / 20% = 35 = 3500%

That should be right😃.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[speedramps]

One question per ticket please.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

Remember we are unpaid volunteers and you are getting free expert help for kudos.

It causes upset when a solver answers 90% and then another solver answers the 10%

Keep it simple and ask one question per ticket. Thnak you.

[NadeemAhamed]

Dear @speedramps ,

Thank you for your valuable time to reply on my post. 

I will post one question per one ticket.