how to get difference from plan vs actual in power bi

[NadeemAhamed]

Hi Everyone,

Greetings!

Thank you in advance.

I am new to Power BI, i have created one P&L report.

i want to take difference in % from plan vs actual for two rows (First & Secound) as shown below table.

When iam trying to fetch the excel data in power bi desktop. its taking column wise Formate like General and Datatype is whole number, but i am not able to change the Formate into %. 

If i change the Formate into % its taking to entire column. but i want to apply only for cell or row not for column

Excel Data

Category	Plan	Actual	variance
first	6	4	-2
second	3	2	-1
difference	53.18%	51.28%	-1.90%
third	41	47	6
fourth	1	-4	-5

Power BI After Fetching data from excel.

[Daniel29195]

in power bi, the datatype affect the whole column . 

it works different than excel, where you can set a datatype per cell. 

[NadeemAhamed]

Ok then is their any solution to provide the difference in % by using measures or else (for First and Secound Rows)

[Daniel29195]

in category, you only have  first, second,  third and fourth ? 

[NadeemAhamed]

Yes i have, but when iam taking this excel data into Power BI its taking as i have explained above. 

but i want to take difference for plan vs actual in row wise. 

[Daniel29195]

not sure if this is exactly what you need  but  : 

i created this table : 

and this is the ouput result : 

if this is what you want  : 

step 1 : you need first to create a column called index 

step 2 :  the row difference convert it into decimal

step 3 :  change the datatype of columns in power query to decimal 

step 4 : sort column category by column order 

step 5  : create 3 measures  : 

plann =

SWITCH(

    TRUE(),

    SELECTEDVALUE('Table (5)'[order]) in {"3","6"} ,  FORMAT(CALCULATE(SUM('Table (5)'[plan]))*100,"%##.#"),

    FORMAT(CALCULATE(SUM('Table (5)'[plan])),"##")

)

actual =

SWITCH(

    TRUE(),

    SELECTEDVALUE('Table (5)'[order]) in {"3","6"} ,  FORMAT(CALCULATE(SUM('Table (5)'[acutal]))*100,"%##.#"),

    FORMAT(CALCULATE(SUM('Table (5)'[acutal])),"##")

)

variancee =

SWITCH(

    TRUE(),

    SELECTEDVALUE('Table (5)'[order]) in {"3","6"} ,  FORMAT(CALCULATE(SUM('Table (5)'[variance]))*100,"%##.#"),

    FORMAT(CALCULATE(SUM('Table (5)'[variance])),"##")

)


finally, drag and drop category to the visual,  and in values drag the 3 measures. 

@NadeemAhamed  

[NadeemAhamed]

@Daniel29195  Thank you for your valuable time to provide the solution.

I am getting one error after trying above steps. "Can't display the visual"

1. I have created index column and 3 measures after column type change.

2. i have used category in Rows and all 3 measures in values

3. Error:- Can't display the visual

 

Error Log:

[Daniel29195]

hey,

in the measure it seems that you are comparing text with integers.

try removing the double quotes   in { "3","6" } 

write it like this :  in { 3,6 } 

 let me know if this works. 

[NadeemAhamed]

@Daniel29195  Thank you soo much for your valuabe time to provide the solution. 

Yes its working fine. 

Now its coming like %53.2 but i need to show the percentage after the number like 53.2%. Is it possible?

[Daniel29195]

yes of course. just  change "%##.#" to "##.#%"

this would work.

[NadeemAhamed]

Dear @Daniel29195,

Its giving the error. 

[NadeemAhamed]

Dear @Daniel29195 ,

I have tried with this also.

result would be :

Can you please guide me to resolve this. 

i want to show the data like 52.1%

[Daniel29195]

could you please send a screenshot of the raw data you are using in power bi ? 

[NadeemAhamed]

[Daniel29195]

the Difference row you need to change it to : 

0.5318        

0.5128

since in the measure you are multiplying by 100 . 

[NadeemAhamed]

Dear @Daniel29195 , 

i have changed the raw data from 53.18% to 0.5318 

but result is same.

In the measures where we are brining this percentage value, i think thier only we need to change the format to bring the percentage as per the standard value.

[NadeemAhamed]

Dear @Daniel29195,

Can you please help me out, how to bring the % after the number, its really helping me a lot. 

[Daniel29195]

is it possible that you can share the pbix file so i can take a look ?  

you can upload it to google drive and send the link to it so i can download it and take a closer look of why things arent working out from your side . 

[NadeemAhamed]

Dear @Daniel29195,

Please find the BOX link to download the Pbix for your reference. 

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

[NadeemAhamed]

Dear @Daniel29195 

I hope you have received the Pbix file for reference.

Thank you in advance. 

[Daniel29195 · Accepted solution]

change the measure to : 

Plann = SWITCH(TRUE(),SELECTEDVALUE(Testing[Order]) in {3}, FORMAT(CALCULATE(SUM(Testing[Plan])),"##.#%"),FORMAT(CALCULATE(SUM(Testing[Plan])),"##"))

l