Sums in Matrix

[sambgv]

Hello, community

My problem is pretty complex ( in my opinion at least 🙂 ) and due to data share compliance I may not post the whole model in the forum, but i'm going to try my best to explain with a fictitious example. 

At my workplace we have to book our work effort. These efforts are booked in certain accounts. Now, in my example there's John. John is part of 4 teams with allocation 15% , 25%, 20%, 25% in the teams Eagles, Tigers, Bears and Foxes respectivelly. Let's take today's date when John made work effort bookings. The total time is 7,5 hours. Because he's a part of 4 teams his time needs to be distributed among the teams with respective allocation %.  (All these allocations do not add up to 100, because John does not have to be in any team). 

So my ultimate objective is to display sums of distributed hours and total sum of booked efforts per day like in the matrix below. The problem is the summing up of duplicate rows for total hours booked, which obviously produces the wrong result. If you take a look at the table below, you can for example see that for Account "A" BookedTime adds up to 8 hours, but it should be just 2 hours for this account on this day. So all in all, the calculation should only compute the sum per account, user and day. 

Do you have any suggestions as to how to fix this problem? 

PS. I know it might seem a little confusing and/or data is missing. Please let me know if this is so, so I can add more info. 

[Daniel29195 · Accepted solution]

@sambgv 

let me know if this works  for you .

measure : 

switch (

true() , 

isinscope( tablename[user] ) || isinscope( tablename[account] ) ,

max(bookedtime (hours) , 

sumx(

summarize(

          tablename,

            table_name[account],

            table_name[account],

            table_name[bookedtime (hours)] 

), 

table_name[bookedtime (hours)] 
)

let me know if this works for you !

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. I would appreciate hitting that kudos 👍🫡

[sambgv]

@Daniel29195 
hi! thank you!  yes, the formula works in the example I provided, but when I applied it in my data model I received an error. A single value for the similar bookedtime (hours) column cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result. 

[Daniel29195]

@sambgv 

sorry , didnt get a notification that you replied. i  think it is because of the many spam questions  which are slowing  the system 

that being said, im glad it worked out for you

always happy to help. appreciate the kudos 🤠

[sambgv]

@Daniel29195 it seems i made a syntax mistake 🙂 it works now,. thanks!