getting the max value out of a column

[mlleverino]

Please see my sample data set below.

Two things I'm trying to do here.

1) determine DAX instruction that will give me the MAX "Revenues/Tool" value available per BU (or overall MAX if I don't use the BU name as a filter) as is done on a pivot table

2) with the Max "Revenue/Tool" on hand, multiply the delta between the MAX value and the non-MAXed values by the Tools count to determine the opportunity

 Here is a sample data set.

BU Name	Revenues/Tool	Tools	Customer
CT	7518	2	Cust A
CT	6643	3	Cust G
CT	6097	4	Cust I
TPS	5680	5	Cust I
TPS	2839	6	Cust A
TPS	1532	7	Cust G

[v-ljerr-msft]

Hi @mlleverino,

Could you try the formula below to see if it works in your scenario?

1)

MAX Revenues/Tool by BU =
CALCULATE (
    MAX ( Table1[Revenues/Tool] ),
    ALLEXCEPT ( Table1, Table1[BU Name] )
)

2)

Opportunity =
VAR maxR = [MAX Revenues/Tool by BU]
RETURN
    SUMX (
        FILTER ( ALL ( Table1 ), Table1[Revenues/Tool] <> maxR ),
        maxR * Table1[Tools]
    )

Regards

[mlleverino]

Looks like the problem i have after inputting your formula is that the Revenues per Tool calculation is a Measure and not a column.

Column 'Revenue - Spares/Repair' in table 'vw_FST_Revenue_Paid_Per_Production_Tool_Report' cannot be found or may not be used in this expression.

[v-ljerr-msft]

Hi @mlleverino,

Could you post your real table structures and the measures you're using with some sample data and your expected result? It's better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards

[mlleverino]

https://1drv.ms/f/s!ArRQV20_oNoxgqJo4SU2VtXf4rIj_g

sorry for the delay.  the link is above.

The data contains the following:

• Cust M and Cust S
• BU Names ES and CT
• Account Types: Systems and Revenue
• Amounts 
  • Amounts with corresponding Systems (in Account type) = system count AS OF the specified reporting period
  • Amounts with corresponding Revenues (in Account type) = sales for that given reporting period

Now, what i want to do is...

Step 1.

SUM up the revenues in FY17 1H (July-Sept) per BU and divide them by the latest system count which will be the count as of Sept (2016-09).  Same thing for FY17 2H (Jan-Mar), dividing those revenues by the system count as of Mar.  the result will give me Revenues per System (RpS).

To understand what I want to do in step 2 is let's assume that the calculation from step 1 results in Cust S has RpS of 30K

Cust M has Rps of 20K

Step 2.

I want to calculate the opportunity for the customer (still by BU) with the lowest RpS by basically calculating the max RpS (30K) minus the lowest RpS (20K) and multiplying the delta by the system count of the customer of the lowest Rps (Cust M) 

Below is what I calculated from the excel version so that you can confirm if your DAX calculations are correcct.

FY17 H1

• Cust M
  • CT Revenues = 532,336
  • Sytems as of Sept = 41
  • Rps = 12,984 (532,336 / 41) 
• Cust S
  • CT Revenues = 440,358
  • Sytems as of Sept = 50
  • Rps = 8,807 (440,358 / 50)

I then calculated the opportunity for Cust S (since it has the lower RpS) by subtracting Cust M's RpS from Cust S to come up with 4,177 delta.  I then multipy this delta by 50 (system count of Cust with lowest RpS) to come up with the Cust's opportunity if it were to match the higher RpS of the other customer. 

[mlleverino]

V-ljerr,

let me look into releasing this information.  thanks for taking the time to look for a solution.