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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MEYERITO
Advocate I
Advocate I

Switch Calculation from one value to another

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 NAMEMODEL 1MODEL 2MODEL 3
A110,5
B0,7501

 

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

1 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.

View solution in original post

5 REPLIES 5
AlexisOlson
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.