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.
Hello everyone,
I am looking for a great solution to solve a model issue I am facing, any idea would be appreciated.
I have one company table defining multiple way to value people count :
COMPANY NAME | MODEL 1 | MODEL 2 | MODEL 3 |
A | 1 | 1 | 0,5 |
B | 0,75 | 0 | 1 |
I need to sum number of people I have based on their company and on the calculation model to apply.
If I have 10 people in Company A, with Model 1 = 10 | Model 2 = 10 | Model 3 = 5
Ideally, I would love to use a slicer to be able to select the calculation model and see result on the fly.
Any guidance to solve this?!
I was looking forward SWITCH function and SUMX + RELATED but I was not able to achieve this.
Best
Solved! Go to Solution.
Since I have nothing else to go on, I'll assume that you have a Company dimension table set up that is related both to the Model table and whatever fact table you're doing a user count on.
In this case, you might consider a measure like this:
WeightedUserCount =
SUMX (
VALUES ( Company[CompanyID] ),
[UserCount] * CALCULATE ( SELECTEDVALUE ( Model[Weight] ) )
)
If your situation is different, then you'll need to explain how.
This will be easier if you unpivot your data like this:
Company Name | Model | Weight |
A | Model 1 | 1 |
A | Model 2 | 1 |
A | Model 3 | 0.5 |
B | Model 1 | 0.75 |
B | Model 2 | 0 |
B | Model 3 | 1 |
Then you can make the Model column into a slicer and write a measure to multiply your count times the selected model's weight.
Thank you Alexis, I unpivoted columns, however I fail to find the right formula to get the final results.
As I am counting people who belong to a company (and then associated weight), what would be the best way to write this?
How are you counting people? I don't see any columns or tables related to people.
DistincCount of User ID or Countrows of the table... I could add a column with 1 if necessary, not a big deal indeed !
Since I have nothing else to go on, I'll assume that you have a Company dimension table set up that is related both to the Model table and whatever fact table you're doing a user count on.
In this case, you might consider a measure like this:
WeightedUserCount =
SUMX (
VALUES ( Company[CompanyID] ),
[UserCount] * CALCULATE ( SELECTEDVALUE ( Model[Weight] ) )
)
If your situation is different, then you'll need to explain how.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |