Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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 ... )
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 ... )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |