Grading accounts in a visual based on sales total

[gingerclaire]

We currently do this in excel but I would like to automate it in Power BI/Powerautomate

Each month we calculate the sales in the last 12 months for each account number and I then allocate it a grade based on its sales and the re-import this into our CRM

In power BI I have a table visual showing Account number and sales in the last 12 months. I can't do this as a calculated column in our sales data as it shows line by line results going all the way back. I need a snapshot.

What is the easiest way for me to add a column to my visual which grades the account as A, B or C depending on their spend?

Thanks in advance 🙂

[Anonymous]

Hi @gingerclaire ,

Greg_Deckler's workaround is good. If possible, please accept his reply as solution. More people will benefit from it.

Thanks.

Best Regards,

Stephen Tao

[Greg_Deckler · Accepted solution]

@gingerclaire Use a measure, something like:

Measure =
  VAR __Spend = SUM( 'Table'[Spend] )
  VAR __Return =
    SWITCH( TRUE(),
      __Spend < 100000, "C",
      __Spend < 500000, "B",
      "A"
    )
RETURN
  __Return