Forum Discussion
Revenue by Customer & Year, Filtered Dynamically by additional field
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!
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.
Hi Cotter2000
The way that I implemented it was to use the dynamic banding as a measure. I would then use any of my data in the columns to then be defined by the measures. In doing it this way it means that I can literally use any column and if I have my banding correct created it would drop then into each of the banding buckets.
And that also gives you the ability to then use any column on the X or Y Axis.