How to Dynamically Update Histogram with Slicer in PowerBI

[Guneui_Lee]

Hello, I have a table with {Date, Product Name, Sales} data. I want to display sales by product name in a histogram with 10 groups of equal intervals. I have successfully created a histogram for the entire period using a vertical bar graph, with groups on the x-axis and the count of dates on the y-axis. I added a column to determine which group (0 to 9) each row belongs to by calculating the minimum, maximum, and interval size.

My question is, in Excel, when I create a histogram and filter the Time column, the groups are recalculated and the histogram updates. I want to achieve the same in PowerBI. When I add a slicer to filter dates, I want the histogram to dynamically update for the filtered range. How should I write the DAX formula to achieve this? Thank you.

[Guneui_Lee]

Thank you for your response.

What I want to achieve is a histogram with groups on the X-axis and the count of data within each group on the Y-axis, as shown in the attached histogram. I am calculating the groups based on the min and max, dividing them into 10 groups.

With the current DAX formula I have, the histogram does not recalculate and only shows parts of the already calculated groups.

GroupNumber =

VAR _SelectedProduct = [Product]

VAR _min = CALCULATE(MINA([Sales]), Sheet1, Sheet1[Product] = _SelectedProduct)

VAR _max = CALCULATE(MAXA([Sales]), Sheet1, Sheet1[Product] = _SelectedProduct)

VAR _range = (_max - _min) / 10   --10 group

VAR _group = INT(([Sales] - _min) / _range)

RETURN IF(_group = 10, 9, _group)

If I calculate the groups as a measure, it works for the selected time range, but I still cannot use this measure on the X-axis of the vertical bar graph. I need to display the groups as a column. When using the measure as a column, it still calculates for the entire period.

This is the histogram I want as a result.

I manually set the start time to configure this group I wanted.

GroupNumber_Want =

VAR _SelectedProduct = [Product]

VAR _StartDate = DATE(2024, 07, 10)

VAR _min = CALCULATE(MINA([Sales]), Sheet1, Sheet1[Product] = _SelectedProduct, Sheet1[Date] >= _StartDate)

VAR _max = CALCULATE(MAXA([Sales]), Sheet1, Sheet1[Product] = _SelectedProduct, Sheet1[Date] >= _StartDate)

VAR _range = (_max - _min) / 10   --10 group

VAR _group = INT(([Sales] - _min) / _range)

RETURN IF(_group = 10, 9, IF(_group < 0, BLANK(), _group))

[Guneui_Lee · Accepted solution]

[Solution]

I added a table to represent separate groups and set it up by using measure to calculate the number of cases for each group. I've noticed that the histogram is generated dynamically based on the lookup period.

[Anonymous]

Hi @Guneui_Lee ,

Based on the testing, the histogram can dynamically update for the date range.

You can also view the following document to learn more information.

Solved: Dynamic Slicer & Histogram - Microsoft Fabric Community

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.