Switch Calculation from one value to another

[Anonymous]

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

[AlexisOlson]

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.

[Anonymous]

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? 

[AlexisOlson]

How are you counting people? I don't see any columns or tables related to people.

[Anonymous]

DistincCount of User ID or Countrows of the table... I could add a column with 1 if necessary, not a big deal indeed ! 

[AlexisOlson · Accepted 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.