Data from a indirect relationship.

[IanSwanepoel]

Hi Guys,

I'm having trouble viewing data over indirect relationships



Above is a view of the relationships.
Every Branch has multiple employees, but some employees also belong to more than one branch, thus for the many to many an an allocation table was made to connect the data.
Every Employee uses multiple systems, and systems are used by many employees thus the user table to connect these.

When viewing the rates by branch, I'd like to see what the total cost is to all systems for that branch. It only gives me the sum of the rates for systems used regardless of the amount of users for the branch. For example if users in that branch use Sys1 and Sys2 which cost 100 and 200 respectively it gives a value of 300, seeing as there are example 10 users I need it to give me,  the (5 users using Sys1)*(100) and the (5 users using Sys2)*(200)

Seeing as these columns are seperated by the Employee Allocation table, my question TL;DR is, is it possible to make calculations across indirect relationships and if so how?

[IanSwanepoel · Accepted solution]

I managed to solve the issue doing the following:
I created a measure that sums up the Rate
         ValueSumRate = SUM(Rates[Rate])
And then I proceeded to SumX that value by its lowest possible level, being User
         UserCost = SUMX(User,[ValueSumRate])
Now regardless of how I display the value it always calculates the rates based on the lowest possible value.

[IanSwanepoel]

Hi @v-yulgu-msft

I have, tried that, I want to display it on a higher level than user though.

I tried using a matrix rather than a table, putting in user hoping it would display on a branch level with user level data, then I can just drill it up so that the users don't display but only the branch, but it does the following:

The highlighted total doesn't show the sum of the values below it 😕

[v-yulgu-msft]

Hi @IanSwanepoel,

---

For example if users in that branch use Sys1 and Sys2 which cost 100 and 200 respectively it gives a value of 300, seeing as there are example 10 users I need it to give me,  the (5 users using Sys1)*(100) and the (5 users using Sys2)*(200)

Have you tried adding the user column into table visual?

Regards,

Yuliana Gu

[IanSwanepoel]

Anyone have any advice for me on this topic?