Forum Discussion
Using the maximum value in a matrix in a calculation
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.
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?