Reply
Ski034
Regular Visitor
Partially syndicated - Outbound

Creating Threshold Buckets for Sum Total while using Date Slicer that can be used in Bar Chart

I have donation transactions that are grouped by an id with relationships built between transaction table, date table, id table.  I want to find the sum of the transaction total that is dynamic to a date slicer. Standalone I have accomplished this:

 

 

Total Giving = 
CALCULATE(
    SUM('Financial Transactions'[Amount]),
    ALLSELECTED('Date')
)

 

 

 Works in isolation and then created a table with my category thresholds (example):

 

 

Giving Categories = 
DATATABLE(
    "Min", INTEGER, 
    "Max", INTEGER, 
    "Category", STRING,
    {
        {0, 500, "0 - 500"},
        {501, 1000, "501 - 1000"},
        {1001, 5000, "1001 - 5000"},
        {5001, 9999999, "5001+"}
    }
)

 

 

I have had some success in making this work within a table, but can't within bar chart. Here is an example of a Giving Category Measure that worked within table, but failed in bar chart due to Lookup:

Donation Category = 
VAR TotalAmount = [Total Donations]
RETURN
LOOKUPVALUE(
    CategoryTable[Category Name], 
    CategoryTable[MinAmount], 
    MAXX(
        FILTER(CategoryTable, 
            TotalAmount >= CategoryTable[MinAmount] &&
            TotalAmount <= CategoryTable[MaxAmount]
        ),
        CategoryTable[MinAmount]
    )
)


Issue: I either create a measure to pull the Total Giving and assign a category via table or within the measure itself and run into these issues:

  • Can't add Measure to Bar Chart due to it not having the categorization
  • Calculated column isnt dynamic to the date filter

 

Find myself going in cirlces. End goal is to have a bar chart with total (Count of Givingid's) on Y axis and Categories (0-500,501-1000, etc) as the x-axis that is dynamic to changing date range. 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

Syndicated - Outbound

@Ski034 have you looked at this blog/view on dynamic segmentation



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

2 REPLIES 2
parry2k
Super User
Super User

Syndicated - Outbound

@Ski034 have you looked at this blog/view on dynamic segmentation



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Syndicated - Outbound

This was really helpful and did get me a lot closer to what I am trying to accomplish :)! Thank you!

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)