Forum Discussion
Summing Totals
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?
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 ... )
3 Replies
You original formula says < 50,000 instead of 5,000.
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.
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 ... )