cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## How to Dynamically Update Histogram with Slicer in PowerBI

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.

1 ACCEPTED SOLUTION
Regular Visitor

[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.

3 REPLIES 3
Regular Visitor

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))

Regular Visitor

[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.

Community Support

Hi @Guneui_Lee ,

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

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.