Forum Discussion

Guneui_Lee's avatar
Guneui_Lee
Regular Visitor
2 years ago
Solved

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 ...
DAX
Histogram
powerbi
Slicer
  • Guneui_Lee's avatar
    Guneui_Lee
    2 years ago

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

Guneui_Lee's avatar
Guneui_Lee
Regular Visitor
2 years ago

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's avatar
Guneui_Lee
Regular Visitor
2 years ago

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