Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |