Forum Discussion
Revenue by Customer & Year, Filtered Dynamically by additional field
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 Zhang
Hi 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!
- GilbertQ
Super User
Hi Cotter2000
Have a look at my blog post, where I explain how to use Dynamic Banding across the entire dataset.
This might be what you are looking for to achieve your outcome.
Hi GilbertQ
Thanks for the link. I read through your post and tried what you suggested. However, if I want to use the Bands on my X or Y axis to show total revenue per band, can I do that using your approach? The 'Amount Bands' is a measure and I can't get that to work in the Axis portion of a chart. I tried it as a Column instead but that didn't work either.
Thanks for any help.
