Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Morning all
i have a basic query, but can't get my head around it.
We have 4 four tables - a date table, an activity table, deals table and a user table.
Every activity is linked to a deal. I would like to calculate the value of deals where an activity has table place. The table relationships like as follows:
The result would be a total per user who completed the activity - i.e. example a has x number of deal with a value of y.
Can you help me?
Firstly, thanks for sharing the model view. Some observations
1. You’re User table does not filter the deals table
2. There is a 1 to many relationship between deals and activities.
Based on 2, even if you turn relationship 2 to be bidirectional, that will make users filter deals (to get value), but value will be multiplied for each instance of an activity.
My guess is.
=calculate(sumx(deals,deals[value]),activities)
but you would have to test it
Thanks Matt!
In may main model, the User table does link to the deal table, but for this particular measure, i don't want to take into account the user allocated to the deal, only who completed the activity. I understand this will lead to some strange data, as two users could work on the same deal, but this is find for this use case.
The 1 to many relationship seems correct (correct me if i'm wrong), but i think you're right that the relationship needs to be bi-directional.
One element i didn't mention in the first query is if a single user had two activities for a deal, worth say £1k, i only want to return £1k, rather than £2k.
I've tried your measure, but it seems to come back with any error message " Too many argument" etc.
I have found a formula which seems to work, but not sure if this the best method:
TotalValue = Calculate ( Sum(Deals[Value]), Filter(Deals,Deals[Deal ID] In VALUES(Activities[Deal ID])) )
Any helps would be amazing!
In my actual tables, i have multi realationship, and as such, some for inactive.
This formula works.
TotalValue = Calculate ( Sum(Deals[Value]), Filter(Deals,Deals[Deal ID] In VALUES(Activities[Deal ID])) )
But if i make the relationships inactive, and use the "USERELATIOSHIP" formala instead, it stops working - see below.
TotalValue = Calculate ( Sum(Deals[Value]), Filter(Deals,Deals[Deal ID] In VALUES(Activities[Deal ID])), USERELATIONSHIP(Activities[Deal ID],Deals[Deal ID]), USERELATIONSHIP('Date'[Date],Deals[Add Date]), USERELATIONSHIP(Activities[User ID],Users[User ID]) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
78 | |
75 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |