Forum Discussion

TJKF's avatar
TJKF
Frequent Visitor
4 years ago
Solved

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. A...
Need Help
  • ToddChitt's avatar
    ToddChitt
    4 years ago

    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. 

TJKF's avatar
TJKF
Frequent Visitor
3 years ago

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?