Forum Discussion
Revenue by Customer & Year, Filtered Dynamically by additional field
Hi everyone,
New to Dax here. I have four fields:
Customer, Product Format, Price, Transaction Year
For each customer AND transaction Year, I'm trying to calculate a sum of Price for any year my end user filters on. For example, for a particular customer in year 2015, I want the revenue bucket to be as follows:
| Product Format | Price | Revenue Bucket |
| Format A | $130 | 7. $125+ |
| Format B | $42 | 7. $125+ |
| Format B | $53 | 7. $125+ |
| Format C | $100 | 7. $125+ |
I've gotten that far by creating the following two fields:
RevenueSum = CALCULATE(SUM('Purchasers'[Price]),ALLEXCEPT('Purchasers','Purchasers'[Customer],'Purchasers'[Transaction 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]<125,"6. $101-$124","7. $125+"))))))
However, I want RevenueSum to dynamically change whenever my users filter on one or more Product Formats. For example, if my end user filters for Format B in their Power BI dashboard, I want RevenueSum to recalculate to $95 and the Revenue Bucket to become $76-$100. If they select Formats A & B, it would recalculate to $225 and Revenue Bucket would go back to "$125+."
Right now, the revenue bucket remains static at "$125+". Is there any way to get my RevenueSum measure to dynamically change based on what the end user is filtering for on Product Format?
If I add ProductFormat to my list of ALLEXCEPT I get sums for every customer/year/format but I don't want that. I want it to roll up by year for whatever combination of product format my end users choose.
Thanks!
10 Replies
Hi Cotter2000
Try the following
1. Create a Band Table with the following columns
RevenueBucket , MinValue, MaxValue
2. Example
RevenueGroup Min Max 0 - 30 0 30 31 - 60 31 60 61 - 90 61 90 91 - 120 91 120 121 - 180 121 180 181 - 240 181 240 241 - 360 241 360 > 361 361 999999 This in only an example , the Revenue Group , Min and MAx Values will depend on the buckets you need.
3. Next crete a measure of RevenueSum , which I presume you already have.
4. Create a measure called ReturnBandGroup
ReturnBandGroup =
CALCULATE(
VALUES (RevenueBand[RevenueGroup]),
FILTER (
RevenueBand,
[RevenueSum] >= RevenueBand[Min]
&& [RevenueSum] <= RevenueBand[Max]
)
)5. Now use this ReturnBandGroup measure in your reports.
If this solves your issue please accept it as a solution and also give KUDOS.
Cheers
CheenuSing
- GilbertQ
Super User
Hi there, depending on how you want to create your bands across your data, my blog post below might be able to assist to make it dynamic no matter which piece of data you choose to put it against.
https://gqbi.wordpress.com/2017/05/23/power-bi-dynamic-banding-across-all-fields/
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 ZhangAnonymous
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.
