Dynamically group spend buckets as a filter, but SUM & display sub groups

[Anonymous]

If this has been answered somewhere, I apologize for the duplication!  Been trying to find a way to do the following, but only with partial success:

1. Group or bucket total revenue by Customer Name (or Customer ID) into spend buckets consisting of "<1k", "1-5K", "5-10K", and "10k+"
2. The sum of total revenue needs to be dynamic based on slicers chosen, including: (based on example data set)
   • Time (Fiscal Quarter & Year) - can be a single quarter or can be a range
   • Product(s)
   • Rental Term
   • Lease Status
3. I'm okay using either the Table or Matrix visualization, as long as it can display the necessary information (see description of issue below & screenshot of tables)

I feel like what I want is almost to return all information & standard revenue from the raw data table (BCN Data), but filter it by the dynamic spend bucket?  Not sure if that's easier than the 2 methods I've found that partially work.

The 2 methods get as far as properly bucketing Account Names (only in Matrix though).  I included screenshots of them - "Billings (Test A)" and "Billings (Test B)".  Not really sure how to paste them in as text without losing formatting.  

The main issue I'm still running into is neither method is able to display all the products for that customer.  I assume there's some kind of row context thing I need to adjust for in my measure(s)?  See the image of the tables/slicers I included:
Example tables & issues

1. The total line for "Cars R Us" shows the correct total sum of $8,000.  However, when you expand it, it only displays "BMW 330", because that's the only Product row that has a sum falling within the selected bucket (5-10K).  The Civic & Camry rows only total to $1,000 each, so I think I'm filtering those out with both methods?
2. In the Table visualization (works for Matrix), my measures incorrectly include "Rent-a-prise".  The total for "Rent-a-prise" within FY19Q4 is actually $38,000 ($30,000 + $8,000), but due to the same row context issue above (?) it actually includes the $8,000 row for Dodge.

Please let me know if there's anything I can add or clarify!  I'm stumped and embarassed to say I've been stuck on this for over a week now.

[Anonymous]

Sorry, forgot to include the example pbix.  Is there a way to upload here?  If not, its here in dropbox: https://www.dropbox.com/s/3n538d6cun7uc2f/Example.pbix?dl=0

[v-xuding-msft]

Hi @Anonymous ,

I have viewed the report. I think you mean that the customer names should be listed totally while filtering. And just the data which is falling within the selected bucket (5-10K) will show. Others will show blank. Do I understand correctly? If so, you could turn on the option of "Show items with no data".

• The total line for "Cars R Us" shows the correct total sum of $8,000.  However, when you expand it, it only displays "BMW 330", because that's the only Product row that has a sum falling within the selected bucket (5-10K).  The Civic & Camry rows only total to $1,000 each, so I think I'm filtering those out with both methods?

Yes, you are right. Only the value of "BMW 330"(8000) is falling into the range of 5-10k. So the result just show it. Others show blank and are hided by default. 

• In the Table visualization (works for Matrix), my measures incorrectly include "Rent-a-prise".  The total for "Rent-a-prise" within FY19Q4 is actually $38,000 ($30,000 + $8,000), but due to the same row context issue above (?) it actually includes the $8,000 row for Dodge.

This scenario is the same as one in Matrix. In your table visual, there are the columns of customer name and product. The data will be summed (8000) by the specific product(Dodge) rather than done(30000+8000) by the customer name(Rent-a-prise).

If I misunderstand, please point me out. And please share the expected results. Then we will be more clear about your request.

[Anonymous]

Hi @v-xuding-msft ,

Thanks for the confirmation!  What you described is the issue I'm trying to resolve.  

The outcome I'm looking to reach (in either table or matrix form) is:

Where only Cars R Us should show up for FY19Q4 - and all the car models are listed & their terms.  Right now due to the row context & how my measure(s) are set up, I show too little for Cars R Us, and too much from Rent-a-prise (shouldn't show up at all).

I'm trying to get my bucketing to sum at the Customer Name level only.  If total revenue from a unique customer name (in selected date range) falls within selected bucket, then I want to display any and all product information associated with that customer.

Hope that helps clarify!

[Anonymous · Accepted solution]

I found a solution: https://www.dropbox.com/s/84goy8d9nw9phun/Example_WithSolution.pbix?dl=0

Its based on this post: https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-ABC-Classification/td-p/479146