Help with relationships

[Pmorg73]

Link to data and Power BI file discussed here:

data and BI file

Hi everyone

I have been learning Power BI for a month or so and I am struggling with relationships. Images below.

I am reporting out of Workflow Max and it uses different tables for Time and Invoicing and I am trying to link the two together. In the example file I have a table that refers to both the Time and Invoice tables and a slicer for staff name. I want to be able to slice the data per person, but to also have the invoice amount totalling correctly when referring to the Time entries. (once working I will work on visuals)

I have avoided using Many to Many after watching may training videos suggesting not doing this. I linked in a job list and a staff list and it is getting half way there, but I just have not figured out relationships properly yet. Can anyone help me getting the Time and Invoice tables talking to each other correcly? My actual data set is several thousand entries in both tables since 2013. Not all jobs are invoiced each month (obviously)

[TomMartens]

Hey,

I guess you are looking for a measure like this:

Measure = 
CALCULATE(
    SUM('Invoice'[[Invoice]] Amount])
    , CROSSFILTER('Job List'[[Job]] Job No.] , 'Time'[[Job]] Job No.] , Both)
)

Using CROSSFILTER allows to avoid many-to-many relationships, but still provides the possibility to use the filter direction "Both".
This allows to create something like this:

But, I'm not sure how you want to treat the fact, that Brenton and Chris have worked on job number "1901-0001" and for this reason the measure shows the same value. But this is not an issue of the measure, but this is due to data distribution. Of course this can be used to calculate some kind of "share".

Hopefully this is what you are looking.

Regards,

Tom 

[Pmorg73]

I am struggling to apply this to my model. I want the column "invoiced Amount total" from table  "Invoiced Time Report" to propogate in the lower visual combined with the table "Workflow Time Report". Getting this same result everywhich way I try to do this.  ?????

My cross filter measure is

Inv Time measure 2 = CALCULATE(SUM('Invoiced Time Report'[[Task]] Amount]) , CROSSFILTER('Job List'[Job No.] ,'Workflow Time Report'[[Job]] Job No.], Both) )

[Pmorg73]

Hi. Might be useful. However. This is my companies invoice against time. We have 50 staff, and typically 3 or 4 people will owkr on any one job. My director asked me to be able to pick out monthly totals for each staff member if I can. So I the duplication of the total will not quite get me what I need.

I will certainly study the measure suggested and may use it in some cases. 

I have wtached videos where they talk about this totals not carrying across tables, but they fix it with relationships. I can never make it work 😞

[TomMartens]

Hey,

as there is no clue in the data how the amount from the invoice table is related to a staff member the "correct" number (from a technical point of view) is provided by the measure, this can not be solved by data modeling, just by additional information or by some kind of business rule like this

per staff member and job no calculate 

Measure * DIVIDE(individual hours, sum of hours by all staff members)

Regards,

Tom

[Pmorg73]

Food for thought. Many thanks

[v-chuncz-msft · Accepted solution]

@Pmorg73 

By the way, you may help accept solution. Your contribution is highly appreciated.