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.
Team,
I have matrix that looks like this (See image 1):
In the rows we have the service name. In the columns we have locations, and the value field is "Product Cost per Case" (Here is how I created this measure)
Product cost = Sum (item price)
Total # of cases = distinctcount (Case#)
Product cpc = Product cost/total# of cases
Now, here is what I am looking for:
In each value field, I want to show the opportunity cost instead of a product cost per case.
Opportunity cost = (Total CPC value – product CPC of that specific field/location) * Total # of cases for that specific field/location
Let’s take orthopedics, MI as an example
Opportunity cost = (36000-22500) *1
Can someone please assist me how to do the above calculation as a measure or any other means ?
Please see image 2 for the data structure
Appreiate your help.
@vmurali1 Maybe:
Opportunity Cost =
VAR __Service = MAX('Table'[Service name])
VAR __Total = SUMX(FILTER(ALL('Table'),[Service name] = __Service),[Item price])
VAR __LocationTotal = SUM('Table'[Item price])
VAR __Result = (__Total - __LocationTotal) * 1
RETURN
__Result
Hey,
Thank you for your response. It did not work. The "*1" is nothing but the # of cases so, i went ahead and replaced that with the # of cases measure that i already have and it still shows error.
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 |
---|---|
12 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
23 | |
3 | |
2 | |
2 | |
2 |