Calculate a Sum of several columns with same Relation

[TripFlow]

Hello Ladies and Gentlemen!

Quit new to PBI and is stuck with the following issue, perhaps, you can help here.

The idea is: to calculate time, which is spent to particular project based on Timesheets, filled by employees.

We have two tables here:

1. Timesheet

Date	Employee	Project_1	Hours_1	Project_2	Hours_2
01.01.21	Jack	A	4	B	2
01.01.21	Mary	B	3	C	5

2. Dictionary, linked to Project_1 (can't have a relation to Project_2 due to tech. restrictions, as I understand).

A

B

C

The ideal situation should look like this:

1. The user Slices the project (based on Dictionary), in which he/she is intersted in (say, B);

2. PBI looks in 'Project_1' and if finds - takes value of 'Hours_1' (3 hours for Mary);

3. PBI looks in 'Project_2' and if finds - takes value of 'Hours_2' (2 hours of Jack);

4. We got a final value of 3 + 2 = 5 hours per Project B.

Quantity of projects is quite big, hard value calculation is not an option.

As of now I've tried different types of something like this:

1. =calculate(sum('Timesheet'[Hours_1]), filter('Timesheet', [Project_1] = [Project_1])))

2. Same for Project 2

3. Sum P1 and P2.

and voodooh dances👹 with Relationships,

but the issue is that PBI does not apply a filter to Project_2 due to lack of Relation (works for Project_1 only).😵

Will much appreciate your advice!

[daXtreme · Accepted solution]

This data format is not suitable for analysis in Power BI. Please unpivot it, like this:

Date	Employee	Project	Hours
01.01.21	Jack	A	4
01.01.21	Jack	B	2
01.01.21	Mary	B	3
01.01.21	Mary	C	5

This is the right format and you'll be able to join this to your dictionary. The measure for total is:

[Total Manhours] = SUM( 'Table'[Hours] )

[TripFlow]

Hi all and thank you for help, I finally did it!

Workaround with unpivoting worked great (lots of guides/videos on how to do it).

Final Measure was something like this: 

Hours_Calc =

calculate(

sum('Timesheet_Unpivoted'[Hours_1]), filter('Timesheet_Unpivoted', 'Timesheet_Unpivoted'[Attribute]="Project_1"))+

calculate(

sum('Timesheet_Unpivoted'[Hours_2]), filter('Timesheet_Unpivoted', 'Timesheet_Unpivoted'[Attribute]="Project_2"))

[daXtreme · Accepted solution]

This data format is not suitable for analysis in Power BI. Please unpivot it, like this:

Date	Employee	Project	Hours
01.01.21	Jack	A	4
01.01.21	Jack	B	2
01.01.21	Mary	B	3
01.01.21	Mary	C	5

This is the right format and you'll be able to join this to your dictionary. The measure for total is:

[Total Manhours] = SUM( 'Table'[Hours] )

[TripFlow]

Hi, thanks for advice! I'll try and will write on results)

[Anonymous · Accepted solution]

Hi @TripFlow ,

Workaround1:

I think you couldn't transform your table only by unpivot function.

Whole M Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjQyUdJa/E5Gwg5QjEJkDsBMRGSrE6KGp8E4sqoXLGQOwMxKZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Employee = _t, Project_1 = _t, Hours_1 = _t, Project_2 = _t, Hours_2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Employee", type text}, {"Project_1", type text}, {"Hours_1", Int64.Type}, {"Project_2", type text}, {"Hours_2", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Employee", "Hours_1", "Hours_2"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Hour", each if [Attribute] = "Project_1" then [Hours_1] else [Hours_2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Hours_1", "Hours_2", "Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Project"}})
in
    #"Renamed Columns"

Result is as below.

Then create a relationship between data table and dimproject table.

Workaround2:

Create inactive relationships between DimProject[Project] with [Project_1] and [Project_2].

Measure:

Measure = 
VAR _Hour1 = CALCULATE(SUM('Table (2)'[Hours_1]),USERELATIONSHIP('Table (2)'[Project_1],DimProject[Project]))
VAR _Hour2 = CALCULATE(SUM('Table (2)'[Hours_2]),USERELATIONSHIP('Table (2)'[Project_2],DimProject[Project]))
RETURN
_Hour1 + _Hour2

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daXtreme]

@TripFlow 

If you want to create good models without any hidden pitfalls... please put aside some time and read this article: Understand star schema and the importance for Power BI … (bing.com) and/or this one: The importance of star schemas in Power BI - SQLBI (bing.com).

If you don't follow the advice in those two articles, you're exposing yourself to issues you won't even be able to diagnose and you'll never know whether or not your model returns the correct values. So, it's of utmost importance to read them.