Forum Discussion

shad_fz's avatar
shad_fz
Regular Visitor
8 years ago
Solved

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.         ...
  • RahulYadav's avatar
    RahulYadav
    8 years ago

    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

MFelix's avatar
MFelix
Super User
8 years ago

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