Forum Discussion
Using the maximum value in a matrix in a calculation
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!
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.
8 Replies
Anonymous
Give this a try. It assumes that your [Value] amount is from a measure.
Max Value = MAXX ( ALLSELECTED ( 'Table'[Customer] ), [Value] )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!
Anonymous
You can write a measure to do the distinct count then use it in my solution.
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?
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])Hi jdbuchanan71 , I just tried that and it did not change my column 4 values.
In your visual, where is the customer name coming from?
