Forum Discussion
Revenue by Customer & Year, Filtered Dynamically by additional field
Thank you both for telling me about Banding. It might be the solution but I'm having trouble making it work. What I want to do is always keep the filters on customer and transaction year. In other words, I want to always sum by those fields, and then let my end user change those sums with additional filtering on Product Format, changing the bands a particular customer falls into dynamically. Here is the table I have created based on some of your suggestions:
| Customer | Year | Format | Revenue | RevenueSum | RevenueBandGroup |
| 1 | 2015 | Format A | 130 | 325 | >$125 |
| 1 | 2015 | Format B | 42 | 325 | >$125 |
| 1 | 2015 | Format B | 53 | 325 | >$125 |
| 1 | 2015 | Format C | 100 | 325 | >$125 |
However, when I filter further by Product Format B, like my end user would, I get the following:
| Customer | Year | Format | Revenue | RevenueSum | RevenueBandGroup |
| 1 | 2015 | Format B | 42 | 325 | >$125 |
| 1 | 2015 | Format B | 53 | 325 | >$125 |
What I want is this:
| Customer | Year | Format | Revenue | RevenueSum | RevenueBandGroup |
| 1 | 2015 | Format B | 42 | 95 | $76-$100 |
| 1 | 2015 | Format B | 53 | 95 | $76-$100 |
In this way, the end user can see how the number of customers in each bucket would change if they eliminated certain product formats from the analysis.
It seems like RevenueSum is my problem. That equation doesn't change dynamically and therefore doesn't force the bands to change. It calculates a static sum by customer and year. Here is the formula.
RevenueSum = CALCULATE(SUM('Purchasers'[Price]),ALLEXCEPT('Purchasers','Purchasers'[Customer],'Purchasers'[Transaction Year]))
How do I get that Sum to then change with additional filters? In Tableau, this would be called a 'Context filter' which would be applied before the static sum is calculated. I just can't figure out the equivalent in Power BI.
Thanks for all your help so far. I really appreciate it.
Directly create your measures using the following DAX, then check if you get expected result.
RevenueSum = SUM('Purchasers'[Price])
Revenue Bucket = IF([RevenueSum]<.01,"1. $0",IF([RevenueSum]<=25,"2. $1-$25",IF([RevenueSum]<=50,"3. $26-$50",IF([RevenueSum]<=75,"4. $51-$75",IF([RevenueSum]<=100,"5. $76-$100",IF([RevenueSum]<125,"6. $101-$124","7. $125+"))))))
Regards,
Lydia Zhang
Anonymous
Hi Lydia,Thanks for your response.
That is actually where I had started. Your equation matches my first attempt. Since it is dynamically summing up Price with no other filters placed on it, it simply copies the revenue from each Product Format/Year/Customer and it assigns it to that Bucket.
Customer Year Format Revenue RevenueSum Revenue Bucket 1 2015 Format A 130 130 7. >$125 1 2015 Format B 42 42.4 3. $26-$50 1 2015 Format B 53 53 4. $51-$75 1 2015 Format C 100 100 5. $76-$100 I'm trying to get the Sum to fluctuate as I pick different product formats, but never fluctuate beyond the boundaries of Year and Customer. If I looked at 10 customers, and I isolated different product formats, it would assign each customer one and only one Revenue Bucket, based on only the Product Formats I've isolated.
Cotter2000,
Would you mind sharing me all the raw data of your table and post expected result here?
Regards,
Lydia ZhangHi Anonymous,
I can't send actual data, but I have faked some data to show you what I am after. Here is the table:
Customer Year Format Revenue 1 2015 A 130 1 2015 B 42 1 2015 B 53 1 2015 C 100 1 2016 A 200 2 2015 A 20 2 2015 D 30 2 2015 E 50 2 2016 D 50 3 2015 A 100 3 2015 A 200 3 2015 B 50 3 2016 B 60 3 2016 B 80 I want to sum revenue and count people by revenue bucket. I add the following two fields to Power BI:
RevenueSum = CALCULATE(SUM(Sheet1[Revenue]),ALLEXCEPT(Sheet1,Sheet1[Customer],Sheet1[Year]))
Revenue Bucket = IF([RevenueSum]<.01,"1. $0",IF([RevenueSum]<=25,"2. $1-$25",IF([RevenueSum]<=50,"3. $26-$50",IF([RevenueSum]<=75,"4. $51-$75",IF([RevenueSum]<=100,"5. $76-$100",IF([RevenueSum]<124,"6. $101-$125","7. >$125"))))))
I then can use the buckets to show revenue and counts of unique customers. But when I then further filter on Product Format, the amount of revenue does not change accordingly.
Correct - prior to format filterFormat Filter applied, no longer correct
When I format for only Product Format B, it should show me no revenue, because no customer in 2015 on their own has $125+ revenue within Format B.
Let me know what you think - thanks!