There might be a time where you're required to extract the maximum value for each row in a chart. In the example below, the maximum sales for each sub-category (i.e. row) across all quarters is indicated by the labels. For example, Q3 was the highest performing quarter for the Art sub-category with $1870 (31%) of sales being made.
If you want the general formula without the full steps then here it is:
IF SUM([Sales]) = WINDOW_MAX(SUM([Sales])) THEN SUM([Sales]) END
After creating this calculation you must edit the table calculation to compute using specific dimensions and then tick the date dimension and untick the dimension.
Note: The example is using the Superstore dataset which has been filtered to show only 2020 data.
1. In order to recreate the view in the diagram we must first drag the date dimension (I have chosen it to be at the quarterly level) onto the columns and sub-category onto the rows.
2. We will now create a calculated field called "% of Total Sales" and place this onto the columns:
SUM([Sales]) / TOTAL(SUM([Sales]))
3. Next, create another calculated field called "Windows Max Sales":
IF SUM([Sales]) = WINDOW_MAX(SUM([Sales])) THEN SUM([Sales]) END
4. After, place this calculated field onto the Label marks card. Right click the field and then click on Edit Table Calculation. Finally, you want to compute using Specific Dimensions and ensure that the Quarter of Order Date is ticked but Sub-Category isn't.
5. Following this, create another calculated field called "Windows Max %":
IF SUM([Sales]) = WINDOW_MAX(SUM([Sales])) THEN [% of total Sales] END
6. You'll want to do the same as you did in the previous step. Place this calculated onto the Label marks card and edit the table calculation.
Do a bit of formatting and you have the final product. Enjoy!
Note: by adding labels we are constrained to how many columns we show, for example the number of months. This is because the labels would take too much space if we displayed the text. If you have a lot of columns then it might be worth ditching the idea of using labels and highlighting the bars with colour along with tooltips instead.
