Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Custom measure

Hello All,

 

I have the 2 following table:

 

ProjectRolesFTE budget  ResourceRolesProjectHours
AAAJC0  111JCAAA2
AAACO5  222COAAA1
AAASC5  333JCBBB5
BBBJC4  444SCCCC2
BBBCO0  555COBBB3
BBBSC3  666PMCCC7
BBBPM5  777COCCC4
CCCJC3      
CCCCO3      
CCCSC2      
CCCPM2      

 

Relationship is:

Project to project (1:*)

 

I need a mesaure to calculate the red column:

 

ProjectRolesFTE_BudgetHours
AAAJC02
AAACO51
AAASC50
BBBJC45
BBBCO03
BBBSC30
BBBPM50
CCCJC30
CCCCO34
CCCSC22
CCCPM27

 

Could you please help me with this measure?

 

Many thanks

  • Here is one way.

    1) Create dimension tables (Modeling -> New Table) for the fields Project and Roles using the follwoing pattern:

    DProject =
    DISTINCT (
        UNION (
            VALUES ( 'FTE Budget Table'[Project] ),
            VALUES ( 'Resource Table'[Project] )
        )
    )
    

    2) Set up single direction 1:* relationships between these dimension tables and their corresponding fields in the fact Tables. The model looks like this:

     3) Create the following measures to use in the visual:

    Sum FTE Budget = 
    SUM('FTE Budget Table'[FTE budget])
    Sum Project Hours =
    IF (
        ISBLANK ( [Sum FTE Budget] ),
        BLANK (),
        SUM ( 'Resource Table'[Hours] ) + 0
    )
    

    4)  create the visual using the fields from the Dimension tables and the measures to get:

    I've attached the sample PBIX file

     

1 Reply

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion

    Here is one way.

    1) Create dimension tables (Modeling -> New Table) for the fields Project and Roles using the follwoing pattern:

    DProject =
    DISTINCT (
        UNION (
            VALUES ( 'FTE Budget Table'[Project] ),
            VALUES ( 'Resource Table'[Project] )
        )
    )
    

    2) Set up single direction 1:* relationships between these dimension tables and their corresponding fields in the fact Tables. The model looks like this:

     3) Create the following measures to use in the visual:

    Sum FTE Budget = 
    SUM('FTE Budget Table'[FTE budget])
    Sum Project Hours =
    IF (
        ISBLANK ( [Sum FTE Budget] ),
        BLANK (),
        SUM ( 'Resource Table'[Hours] ) + 0
    )
    

    4)  create the visual using the fields from the Dimension tables and the measures to get:

    I've attached the sample PBIX file