Forum Discussion
Perfect Difference Bar Chart
Hi All,
I am very new to Power BI. I was wondering if there is a way to measure the percent different between planned and actual hours in the in the following chart? Thanks.
Hi shad_fz,
Follow below steps for calculating percentage difference.
1. Add New Column "Planned Hours"
Planned Hours = if('Planned Vs Actual'[Planned or Actual Hours]="Planned",'Planned Vs Actual'[Total Hours],0)2. Add New Column "Actual Hours"
Actual Hours = if('Planned Vs Actual'[Planned or Actual Hours]="Actual",'Planned Vs Actual'[Total Hours],0)3. Create a new Quick Measure. Right Click on Table name & select New Quick Measure
a. Select "Percentage Difference" under "Mathematical Operations".
b. Drag "Planned Hours" column to "Base Value".
c. Drag "Actual Hours" column to "Value to compare"
d. Blanks: Treat as zero in the calculation
4. This will add below measure to the table.
Actual Hours % difference from Planned Hours = VAR __BASELINE_VALUE = SUM('Planned Vs Actual'[Planned Hours]) VAR __VALUE_TO_COMPARE = SUM('Planned Vs Actual'[Actual Hours]) RETURN DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)Please let me know if it helps.
Thanks,
Rahul
2 Replies
Hi shad_fz,
Follow below steps for calculating percentage difference.
1. Add New Column "Planned Hours"
Planned Hours = if('Planned Vs Actual'[Planned or Actual Hours]="Planned",'Planned Vs Actual'[Total Hours],0)2. Add New Column "Actual Hours"
Actual Hours = if('Planned Vs Actual'[Planned or Actual Hours]="Actual",'Planned Vs Actual'[Total Hours],0)3. Create a new Quick Measure. Right Click on Table name & select New Quick Measure
a. Select "Percentage Difference" under "Mathematical Operations".
b. Drag "Planned Hours" column to "Base Value".
c. Drag "Actual Hours" column to "Value to compare"
d. Blanks: Treat as zero in the calculation
4. This will add below measure to the table.
Actual Hours % difference from Planned Hours = VAR __BASELINE_VALUE = SUM('Planned Vs Actual'[Planned Hours]) VAR __VALUE_TO_COMPARE = SUM('Planned Vs Actual'[Actual Hours]) RETURN DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)Please let me know if it helps.
Thanks,
Rahul
Hi shad_fz,
Although the solution provided by RahulYadav it's a good solution it will add upp to increase your model in size because every calculated column you add to your model it will add size and complexety to your data.
This can be solved making use of a measure for all the parts of RahulYadav solution add this measure to your model:
Actual Hours % difference from Planned Hours = VAR BASELINE_VALUE = CALCULATE ( SUM ( 'Planned Vs Actual'[Planned Hours] ), 'Planned Vs Actual'[Planned or Actual Hours] = "Planned" ) VAR VALUE_TO_COMPARE = CALCULATE ( SUM ( 'Planned Vs Actual'[Actual Hours] ), 'Planned Vs Actual'[Planned or Actual Hours] = "Actuals" ) RETURN DIVIDE ( VALUE_TO_COMPARE - BASELINE_VALUE, BASELINE_VALUE )As you can see this measure calculates the planned / actuals hours and based on context so it will be calculated at each update.
Regards,
MFelix
