Forum Discussion
Adding an extra column to a matrix chart
I have a matrix chart that has a list of companys and then sales, market potential and share of wallet in different product categories. There are also some conditional formatting in the SOW column. All works well. Example:
The data comes from a table to which I have added a calculated column to say yes when it has been more than 2 years from the last sale. I'd like to have an alarm column added to the chart which alerts the user when there has been no sales for 2 or more years. Another way of doing this would be ok, but this comes to my mind first. For exampe the company name could turn red, but I don't think this is possible. Wanted end result:
The data I have is in a table like this:
Challenges I have are:
- There can be several dates for sales in the same column for same product group and company. How do I find out the latest sales date and use it as the basis of the decision if that company needs to have alert or not?
- What would be the best way to have the alrert added to the matrix chart?
Thank you for your help!
Add a table to the model (either in Power Query or DAX) that aggregates the main table by Company and Product Group. Get the MAX(Sale Date) as an aggregated column. Now create a calculated column in this table and the main table that is a concatenation of Company and Product Group. Example: [My Join Column] = [Company] + "-" + [Product Group]. Join the aggregated table to this one on the new columns.
You cannot calculate this Alarm column in the context of a single row in your main table. Why? Suppose you have a sale that is 3 years old. For that, Alarm = Yes. Then you also have a sale that was a month ago. Alarm = No for that row. You need the first row to now say No becasuse of the recent sale.
2 Replies
- ToddChitt
Super User
Add a table to the model (either in Power Query or DAX) that aggregates the main table by Company and Product Group. Get the MAX(Sale Date) as an aggregated column. Now create a calculated column in this table and the main table that is a concatenation of Company and Product Group. Example: [My Join Column] = [Company] + "-" + [Product Group]. Join the aggregated table to this one on the new columns.
You cannot calculate this Alarm column in the context of a single row in your main table. Why? Suppose you have a sale that is 3 years old. For that, Alarm = Yes. Then you also have a sale that was a month ago. Alarm = No for that row. You need the first row to now say No becasuse of the recent sale.
Hi and sorry for my delayed reply. I was busy elsewhere for a few weeks.
This solves the first part of my challege. Thank you for your help!
I haven't figured out yet what would be the best way to add an alarm to the matrix. The closest solution is that I add the column which says 'alarm' if that company hasn't had any sales for the last 2 years to the matrix values field. This will then add another column for each product category which is not ideal. Any ideas?
