topic Re: Calculate a Sum of several columns with same Relation in DAX Commands and Tips

Calculate a Sum of several columns with same Relation

TripFlow — Mon, 29 Aug 2022 20:06:05 GMT

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).

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!

Re: Calculate a Sum of several columns with same Relation

daXtreme — Mon, 29 Aug 2022 20:35:43 GMT

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] )

Re: Calculate a Sum of several columns with same Relation

TripFlow — Mon, 29 Aug 2022 20:39:05 GMT

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

Re: Calculate a Sum of several columns with same Relation

daXtreme — Tue, 30 Aug 2022 07:05:26 GMT

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.

Re: Calculate a Sum of several columns with same Relation

Anonymous — Thu, 01 Sep 2022 07:30:03 GMT

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.

Re: Calculate a Sum of several columns with same Relation

TripFlow — Thu, 01 Sep 2022 09:17:44 GMT

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"))