topic Re: Creating histogram using measure and dimension from data warehouse in DAX Commands and Tips

Creating histogram using measure and dimension from data warehouse

Anonymous — Sun, 01 Dec 2024 04:27:36 GMT

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.

Re: Creating histogram using measure and dimension from data warehouse

Fowmy — Sun, 01 Dec 2024 07:14:15 GMT

Anonymous

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.

Re: Creating histogram using measure and dimension from data warehouse

Poojara_D12 — Mon, 02 Dec 2024 05:06:32 GMT

Hi Anonymous

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

Re: Creating histogram using measure and dimension from data warehouse

Anonymous — Tue, 03 Dec 2024 06:34:16 GMT

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

Hi, Anonymous

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.

Re: Creating histogram using measure and dimension from data warehouse

Anonymous — Tue, 03 Dec 2024 06:42:58 GMT

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.