Summing Totals

[Anonymous]

I have a table that has company Id's, invoice dates, and revenue amounts. I'm attempting to create revenue ranges (eg. $1-$5,000, $5,000-$10,000 etc.) but it is trickier than I imagined. So I have this so far:

So obviously none of these are right. When trying to find out why, it's because of the invoice dates which are entered row by row. If I add those invoice dates, the table then makes sense:

 

But this is not what I'm looking for. I would like to be able to have a formula use whatever the sum is for each company to determine their $ range instead of having it look at individual invoice dates.

The last part is having it used in conjunction with a date slicer from my date table. So I don't know if it's possible, but the $ range column I suppose could change for a given company based on what is selected on the MIN/MAX ends of the date slicer.

So for instance, company 0022 has a grand total of $16,642.24, so by default it would be assigned a range of $10,000-$20,000. If I wanted to look at a one year period of their revenue using my date slicer, let's say the revenue changes to $3,081.79 to reflect that. The range then would change to $1,000-$5,000. Is this possible?

[Greg_Deckler]

You original formula says < 50,000 instead of 5,000.

[Anonymous]

@Greg_Deckler thank you for catching that! Would you happen to know how can I transform this so I can get a formula that will give me a total of the revenue by company rather than line by line depending on the invoice date? I'm looking for my end result to be like this:

So this way, it's not deciding line by line of what $ range this company should be in but instead looking at the grand total the company has earned. I got the above by summarizing my revenue table and then establishing a 1-to-many relationship between the 2 tables, but I need the highlighted column in a dax format rather than from a summarized table so I can use it in a formula.

[Greg_Deckler · Accepted solution]

If your visual is according to your table below, then you could do something like:

Measure =
VAR __co = MAX('Table'[Co])
VAR __total = SUMX(FILTER('Table',[Co] = __co),[Revenue])
RETURN
SWITCH(TRUE(),__total>0 && __total < 5,000 ...  )