none
Planned VS ACtual RRS feed

  • Question

  • Hello I am new to power bi

    I am having difficulty calculating planned vs actual

    I am unable to calculate the gran total of planned:

    Sample file is here:

    https: // onedrive.live.com/?cid=2C972B929D043868&id=2C972B929D043868%21105&parId=2C972B929D043868%21104&o=OneUp

    The measures I have used are:

    Total Hours = CALCULATE(SUM(Fact_Table[ACTIVITY_DURATION (Hours)]),Dim_Table1[Category]="Planned",Dim_Table1[Billable Rate]=1)

    PLANNED_HOURS =

    VAR T2 = CALCULATETABLE(
        Fact_Table,
        FILTER(
            Fact_Table,
            Fact_Table[CHANNEL]="PLANNED"
        ),FILTER(Dim_Table1,Dim_Table1[Billable Rate]=1))
    RETURN
        CALCULATE (

            SUMX(T2,Fact_Table[ACTIVITY_DURATION (Hours)]))

    ACTUAL_HOURS =
    VAR T1 = CALCULATETABLE(
        Fact_Table,
        FILTER(
            Fact_Table,
            Fact_Table[CHANNEL]="ACTUAL"
        ),FILTER(Dim_Table1,Dim_Table1[Billable Rate]=1))
    RETURN
        CALCULATE (
            SUMX(T1,Fact_Table[ACTIVITY_DURATION (Hours)]))

    Desired Result:

    Calculate Actual for each category as a percentage of grand total of planned

    Calculate PLANNED as % of total planned

    Monday, July 8, 2019 8:25 PM

All replies

  • I think I got it.  Please let me know if there is a better way

    Total Hours = CALCULATE(SUM(Fact_Table[ACTIVITY_DURATION (Hours)]),Filter(all(Dim_Table1),(Dim_Table1[Billable Rate])&&Dim_Table1[Source ]="planned"))

    Tuesday, July 9, 2019 9:53 PM
  • This was helpful:

    community.powerbi.com/t5/Desktop/How-to-Use-Aggregate-function-In-PowerBi/td-p/255304

    Tuesday, July 9, 2019 10:27 PM