Using the maximum value in a matrix in a calculation

[Frenchtom811]

Hi Folks!  I have the following matrix:

Customer	Value
Acme	56
Gadget	78
Widget	34
Swag	79
Spacely	54
Duff	18

I want to use the largest value in the matrix to calculate the % complete represented by each customer.  This assumes that the largest value is the 100% complete value.  My new matrix, which I am struggling to build, should look like this:

Customer	Value	Max	% complete
Acme	56	79	.71
Gadget	78	79	.99
Widget	34	79	.43
Swag	79	79	1.0
Spacely	54	79	.68
Duff	18	79	.23

The complete column is simply [Value]/[Max].  The solution needs to be flexible because as I select different slicer values, the customer names will change.  So far I have tried the SUMMARIZE function to calculate my Max column values but with no luck.  Any ideas?  It is the values in the MAX column that I am struggling with.

TIA!   

[jdbuchanan71]

You should not be looking for ALLSELECTED('Table1'[Item Number]).  You want ALLSELECTED customers

Max Customer Count = MAXX(ALLSELECTED('Table1'[Customer Name]),[Average SKU Count])

[Frenchtom811]

Hi @jdbuchanan71 , I just tried that and it did not change my column 4 values.

[jdbuchanan71]

In your visual, where is the customer name coming from?

[Frenchtom811 · Accepted solution]

Hello @jdbuchanan71 , I figured it out with the help of this article.

The equations that solved everything were 

Max SKU Count = MAXX(SUMMARIZE(ALLSELECTED('Table1').'Table1'[Customer Name],"MSKU",COUNT('Table1'[Pricelist SKU Count])),[MSKU])

where Pricelist SKU Count = a calculated column

Pricelist SKU Count = MAXX('Table1',[Pricelist SKU Count Temp])

where Pricelist SKU Count Temp = a measure

Pricelist SKU Count Temp = DISTINCTCOUNT('Table1'[Item Number])

The mistake I kept making was in the top "Summarize" equation.  I started by using DISTINCTCOUNT and kept getting "1" returned as a value.  I changed it to COUNT and voila, problem solved!  I admit that there are likely bettre ways this can be done but I am happy with it as is.

[Frenchtom811]

Hi @jdbuchanan71 .  I have made several attempts but still no luck.  Please refer to the attached screen grab.

The first column contains my customers.  These names change depending on slicers that allow users to filter by select customer types, customer locations, etc. 

The second column, Pricelist Item# Count, is a distinct count of all the item numbers that appear on a customer specific price list.  This column is calculated by placing [Item Number] in the Values section of the Matrix and selecting Count (Distinct) from the options (other options include First, Last and Count). 

The third colum, Average SKU Count (a poor column name) is calculated as follows: Average SKU Count = DISTINCTCOUNT('Table1'[Item Number]).  Functionally, this is equivalent to the second column. 

The fourth column, Average SKU Count3 (another poor column name) contains your formula written as Average SKU Count3 = MAXX(ALLSELECTED('Table1'[Item Number]),[Average SKU Count]).  As you can see, I get identical values per customer in column 4 as I do in columns 2 and 3.  In fact, everything I try produces the output you see above in columns 2 - 4.  What I am trying to get in column 4 for all customers is the circled value which in the above example would be "1678".  This value will change with slicer selections.  

I will use the maximum distinct count to determine an alignment score for each customer.  In this example, the customer with distinct count = 1678 will have a 100% alignment score, the customer with distinct count = 69 will have a 69/1678 or 4% alignment score.  

Any thoughts?

[Frenchtom811]

Hi @jdbuchanan71 !  My Value amount is not a measure, it is a distict count of a text field measure.  I beleive this changes the proposed solution as I could not get your suggestion to work.

Thanks!

[jdbuchanan71]

@Frenchtom811 

You can write a measure to do the distinct count then use it in my solution.

[jdbuchanan71]

@Frenchtom811 

Give this a try.  It assumes that your [Value] amount is from a measure.

Max Value = MAXX ( ALLSELECTED ( 'Table'[Customer] ), [Value] )