Creating histogram using measure and dimension from data warehouse

[wokka]

Hi

I use one dimension and one measure to create the simple 2 column table, then change it to a clustered column chart by choosing "clustered column chart" from Visualisations area.  The chart shows the individual Measure values along the x-axis as columns with the dimension as the Y axis.

However, rather than just have columns of individual values, we would like to condense down the data into a histogram with ranges and frequencies. I have read that I can create groups and buckets etc and do it that way, but our powerbi setup for some reason wont allow us to create groups.  So to work around that issue, do I need to export the data from the table/graph to a new separate powerbi file that will allow groups to be created? ( or maybe just export to excel and do histogram there? )

I have also tried a couple of histogram visuals from marketplace, but they seem a bit flaky.

We would be grateful to know who to code up a solution of a histogram via DAX, maybe it can be done via maybe custom measures drawing data from the original table?  but am not sure how it would be done?

Thanks in advance.

[Anonymous · Accepted solution]

Thanks for the reply from Poojara_D12  and Fowmy , please allow me to provide another insight:

Hi, @wokka 

Have the answers from Poojara_D12  and Fowmy resolved your issue? If so, please mark their responses as the solution. This will help others with similar questions find the answers more quickly.

Regarding your question, I have also created the following example:

1.Firstly, here is my sample data:

Bins = 
DATATABLE(
    "Bin", INTEGER,
    {
        {0},
        {10},
        {20},
        {30},
        {40},
        {50},
        {60},
        {70},
        {80},
        {90},
        {100}
    }
)

2.Secondly, I have created the following calculation table:

3.Next, I have created the following measures:

Frequency = 
COUNTROWS(
    FILTER(
        TestData,
        TestData[Value] >=MAX('Bins'[Bin])&& TestData[Value] < MAX('Bins'[Bin]) + 10
    )
)

4.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[wokka]

Hi

Thank you for the solution. I think switching from SQL Server thinking to DAX thinking is not easy.

It appears Microsft removed its perfectly good histogram visualization from the Powerbi market place in 2022.  It would be good for that to be added back in again.

Excel has histogram graphs built in, why doesnt powerbi? Its a mystery.

[Poojara_D12]

Hi @wokka 

You can create a histogram in Power BI using DAX without exporting data. Here’s a simple way:

1. Create Ranges for the Histogram:
- Add a calculated column to your table in Power BI to define buckets/ranges. For example:

Range =
SWITCH(
TRUE(),
[Measure] <= 10, "0-10",
[Measure] <= 20, "11-20",
[Measure] <= 30, "21-30",
"31+"
)

2. Build the Histogram:
- Use the Range column as the X-axis (Legend/Axis) in your chart.
- Use a measure like `Count` or `Sum` of the original values for the Y-axis.

3. Refine the Chart:
- Format your clustered column chart to show the ranges clearly.

This method avoids relying on groups or exporting data. You can adjust the ranges in the DAX formula as needed!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[Fowmy]

@wokka 

If you could add calcualted column to your table with the following formula and visualize your histogram, it would work too. This would be static though.

Bin = 
SWITCH(
    TRUE(),
    [Value] >= 0 && [Value] <= 10, "0-10",
    [Value] >= 11 && [Value] <= 20, "11-20",
    [Value] >= 21 && [Value] <= 30, "21-30",
    [Value] >= 31 && [Value] <= 40, "31-40",
    "Other"
)

The other option is to have  disconnected table and do it with a measure as.