Revenue by Customer & Year, Filtered Dynamically by additional field

[Cotter2000]

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!

[Cotter2000]

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. 

[v-yuezhe-msft]

@Cotter2000,

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

[Cotter2000]

@v-yuezhe-msft

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. 

[v-yuezhe-msft]

@Cotter2000,

Would you mind sharing me all the raw data of your table and post expected result here?

Regards,
Lydia Zhang

[Cotter2000]

Hi @v-yuezhe-msft,

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]

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.

Power BI – Dynamic Banding across all fields

[Cotter2000]

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.

[GilbertQ]

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.

[GilbertQ]

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/

[CheenuSing]

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