Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
IanSwanepoel
Helper I
Helper I

Data from a indirect relationship.

Hi Guys,

I'm having trouble viewing data over indirect relationships

Capture4.PNG

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.

Capture5.PNG

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?

1 ACCEPTED SOLUTION
IanSwanepoel
Helper I
Helper I

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.

View solution in original post

4 REPLIES 4
IanSwanepoel
Helper I
Helper I

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
Helper I
Helper I

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:
Capture2.PNG
The highlighted total doesn't show the sum of the values below it 😕

v-yulgu-msft
Employee
Employee

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

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
IanSwanepoel
Helper I
Helper I

Anyone have any advice for me on this topic?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.