topic Requesting help with Dax Calculation in Developer

Requesting help with Dax Calculation

vmurali1 — Sun, 19 Feb 2023 23:53:10 GMT

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.

Re: Requesting help with Dax Calculation

Greg_Deckler — Mon, 20 Feb 2023 00:44:44 GMT

@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

Re: Requesting help with Dax Calculation

vmurali1 — Mon, 20 Feb 2023 01:52:11 GMT

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.