Totals in matrix from different tables depending on field value in the table

[Linq]

Hello everyone, I'm stucked with a problem I'm trying to deal for a couple of days now... My dataset contains tables related by one to may relationship type. Those are: Customer > Projects (related by Customer ID) > Tasks (related by ProjectID) > Activities (related by TaskID).

Additionally, Activities table is related with calendar table (by [Date of activity]. There is another table named RecurringProjects which is related with Projects table by [ProjectID] and with Calendar by [PeriodStartDate].

In the Projects table I simply have ID, CustomerID, Name, Type (StandardProject/RecurringProject/Implementation), StartingDate, EndDate and Status (acvite/finished).

In the RecurringProjects I have ID (incremental from database), ProjectID, StartPeriod, TimeBudgetAmount and CashBudgetAmount.

The deal with two project tables is I have separate data for two different kind of projects. Standard projects (e.g. NEW WEBSITE PROJECT) is separated on tasks (e.g. BACKEND, FRONTEND) and tasks has their time budget and cash budget (e.g. BACKEND has 30h and $30 budget and FRONTEND has 20h and $20), so the project will have 50h time budget and $50 cash budget.

Second type of project is recurring projects (different table and filling mechanism in a database) which has time budget for every month and is related to the whole project rather than a task (e.g. WEBSITE SUPPORT PROJECT which starts on 1st of March '20 and is still running will have three rows in RecurringProjects table:

1) ProjectID 5; StartPeriod 03/01/2020; TimeBudgetAmount 20; CashBudgetAmount $20

2) ProjectID 5; StartPeriod 04/01/2020; TimeBudgetAmount 10; CashBudgetAmount $10

3) ProjectID 5; StartPeriod 05/01/2020; TimeBudgetAmount 5  ; CashBudgetAmount $5

And now here comes the problem - what I need to achieve is to display a matrix containing Customer and ProjectName in rows and Type(Standard/Recurring/Implementation), TimeUsed, [TimeBudget], [CashBudget], [CashUsed] and [Outcome $] in values.

The last four values are measures:

1) TimeBudget - it has to be sth like < if ProjectType is 'Recurring' then SUM(RecurringProjects.TimeBudgetAmount) else SUM(Tasks.TimeBudget) >

2) CashBudget - similar - < if ProjectType is 'Recurring' then SUM(RecurringProjects.CashBudgetAmount) else SUM(Tasks.CashBudgetAmount) >

3) CashUsed - is has to be TimeUsed (sum of work hours from Acitivities table spent on each task related to project) multiplied by $1

4) Outcome - it should be CashUsed subtracted from CashBudgetAmount and - to get it more difficult - it should include time period selected on the calendar.

Therefore for above examples, assuming that both projects refer to the same customer, if you select 03/2020 it should show two rows on a matrix:

     CUSTOMER1

    | |__> 1) NEW WEBSITE PROJECT ; Implementation ; 10h ; 50h ; $50 ; $10 ; $40

             2) WEBSITE SUPPORT PROJECT    ; Recurring ;   2h ; 20h ; $20 ;   $2 ; $18

TOTAL ------------------------------------------------     12h ; 70h ; $70 ; $12 ; $58

I tried simple IF(... , SUM(), SUM()) but unfortunately TOTALS are not working....

Would be grateful for any advice.

[VijayP]

@Linq  Is this possible to create a small excel spread sheet with these numbers and also how you need Matrix in the form excel spreadsheet i can answer you quickly

[jstorm]

You need an associative table for "Project Type" to relate your two project tables.  Then add 'Project Type' as a row along with customer and project name.  Make measures for each column you are trying to show using a CALCULATE( SUM( Recurring ) + SUM(Projects[Field]), [Filter1]...).

[Linq]

You mean I should create table named "Project Type" which will contain column [ProjectID] and [TYPE] and will be related with Projects table by ID - ProjectID and with RecurringProjects by ID - ProjectID ?

[jstorm]

Sorry, I may have misread what you are trying to achieve. Can you post a screenshot of your Model?

[Linq]

Sorry, but the lables are in other language. My model looks like this:

Of course, every relationship is one to many type with arrow direction from one to many.

[jstorm · Accepted solution]

You should be able to aggregate the recurring and regular project values into one CALCULATE function, then the matrix will accomplish the IF logic using the customer and project type rows you specify. So something like...

Time =

CALCULATE(
    SUM( RecurringProjects[TimeBudgetAmount] ) +
    SUM( Tasks[TimeBudget] ),
    Calendar[Date]
)

[Linq]

OMG, this doesn't even need filter parameter for CALCULATE or drill down level with Project Type :).

Thanks a ton 😄

[jstorm]

Power BI gets the best of us all! Glad you could get it worked out 👍