Forum Discussion

mrmars's avatar
mrmars
New Member
8 years ago
Solved

[newbie] Calculations between two tables

Hi everyone, I'm new in Power BI (and databases in general). I'd like to ask your help for a blocker I cannot overtake.

 

I have two tables, the first contains planned hours/revenues for a number of resources, grouped by month, the second contains actual hours/revenues for the same number (or less) resources, grouped by day (anyway, month is contained).

 

The first looks like this:


 

The second one looks like this:

 

My goal is to join the two tables above, grouping by month and considering also people who is not present in the second (Cindy):


 

TOP of the TOPS, I'd like also to have running totals (same table above, plus columns highlighted in yellow):

 

Maybe it's worth it to mention that the first table is coming from an excel file of which I have the total control, so I can change it as I prefer, the second one comes from SSAS and it's basically read only.

 

Is what I need something feasible, or I am asking too much (...I dont think so :smileytongue:, it's just me that I'm a rookie).

 

Thanks to anyone who will help me.

  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    HI mrmars

     

    I reckon I am close with the following.

     

    1. Create a new calculated table using the following code.  I have attached the model in a PBIX file.  I had to change your [Month] columns to actual Date datatypes to get the cumulative calculation going.

     

    New Table = 
VAR BaseTalble =  
    SUMMARIZECOLUMNS(
        'planned'[Name],
        'planned'[Month],
        "Planned Hours",SUM('planned'[Planned Hours]),
        "Planned Revenue",SUM('planned'[Planned Revenue])
        )
        
        
VAR AddActual =   
    ADDCOLUMNS( 
        BaseTalble,
        "Actual Hours",SUMX(FILTER('actual','actual'[Name] = EARLIER('planned'[Name]) && 'actual'[Month] = EARLIER('planned'[Month])),'actual'[Actual Hours]) ,
        "Actual Revenue",SUMX(FILTER('actual','actual'[Name] = EARLIER('planned'[Name]) && 'actual'[Month] = EARLIER('planned'[Month])),'actual'[Actual Revenue])
            )
RETURN AddActual

    Then add the following two calculated columns to the new calculated table/

     

    Running Actual Revenue = 
    SUMX(
        FILTER(
            'New Table',
            'New Table'[Name] = EARLIER('New Table'[Name]) && 
            'New Table'[Month] <= EARLIER('New Table'[Month])
            ),
            'New Table'[Actual Revenue])
    Running Planned Revenue = 
    SUMX(
        FILTER(
            'New Table',
            'New Table'[Name] = EARLIER('New Table'[Name]) && 
            'New Table'[Month] <= EARLIER('New Table'[Month])
            ),
            'New Table'[Planned Revenue])

     

     

4 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    HI mrmars

     

    I reckon I am close with the following.

     

    1. Create a new calculated table using the following code.  I have attached the model in a PBIX file.  I had to change your [Month] columns to actual Date datatypes to get the cumulative calculation going.

     

    New Table = 
VAR BaseTalble =  
    SUMMARIZECOLUMNS(
        'planned'[Name],
        'planned'[Month],
        "Planned Hours",SUM('planned'[Planned Hours]),
        "Planned Revenue",SUM('planned'[Planned Revenue])
        )
        
        
VAR AddActual =   
    ADDCOLUMNS( 
        BaseTalble,
        "Actual Hours",SUMX(FILTER('actual','actual'[Name] = EARLIER('planned'[Name]) && 'actual'[Month] = EARLIER('planned'[Month])),'actual'[Actual Hours]) ,
        "Actual Revenue",SUMX(FILTER('actual','actual'[Name] = EARLIER('planned'[Name]) && 'actual'[Month] = EARLIER('planned'[Month])),'actual'[Actual Revenue])
            )
RETURN AddActual

    Then add the following two calculated columns to the new calculated table/

     

    Running Actual Revenue = 
    SUMX(
        FILTER(
            'New Table',
            'New Table'[Name] = EARLIER('New Table'[Name]) && 
            'New Table'[Month] <= EARLIER('New Table'[Month])
            ),
            'New Table'[Actual Revenue])
    Running Planned Revenue = 
    SUMX(
        FILTER(
            'New Table',
            'New Table'[Name] = EARLIER('New Table'[Name]) && 
            'New Table'[Month] <= EARLIER('New Table'[Month])
            ),
            'New Table'[Planned Revenue])