Forum Discussion
Histogram based on measure values
Hi,
I`m trying to build a Histogram (Column Chart) that shows the following:
x-axis: Bins based on Sum earnings for each Simulation
y-axis: Count of those simulations
Here is sample data:
| Asset Scenario | FY | Simulation Number | Earnings |
| 2 | 2030 | 1 | 5075934 |
| 2 | 2031 | 1 | 14049834 |
| 1 | 2030 | 1 | 43520733 |
| 1 | 2031 | 1 | 76475741 |
| 2 | 2030 | 2 | 52532894 |
| 2 | 2031 | 2 | 52761483 |
| 1 | 2030 | 2 | 42437395 |
| 1 | 2031 | 2 | 9337237 |
| 1 | 2030 | 3 | 54247547 |
| 1 | 2031 | 3 | 90341398 |
| 2 | 2030 | 3 | 71665715 |
| 2 | 2031 | 3 | 47382286 |
| 1 | 2030 | 4 | 60142877 |
| 1 | 2031 | 4 | 63004168 |
| 2 | 2030 | 4 | 55178590 |
| 2 | 2031 | 4 | 28560290 |
| 1 | 2030 | 5 | 60202994 |
| 1 | 2031 | 5 | 40427671 |
| 2 | 2030 | 5 | 45617474 |
| 2 | 2031 | 5 | 98480761 |
I was able to create a Column Chart BUT based on a column for the x-axis which is not subject to slicers (I will be using Asset Scenario & FY as slicers)
The chart is also based on the full data, won`t match the sample data provided.
Any way I can build this?
I think I`ll need to:
1. Write a measure that returns the sum of earnings for each simulation number
2. Build Bins based on that
3. Be able to use those bins for x-axis
But I`m not sure how to establish those
4 Replies
Many thanks Ashish_Mathur for your help, this has solved the challenge for me.
For future readers, I had to change a few things to customise the provided solution to my case:
1. Removed legend
2. Expanded the bins table to match ranges of my actual data and desired bin size
3.a Created measure [E2] = DistinctCount(Simulation number) as I was looking to show that instead of measure [E]= Sum(Earnings)
3.b adjusted the provided [E by bins] based on previous point, still needed to use [E] and the formula became like this:
E by bins = CALCULATE([E2],FILTER(ADDCOLUMNS(VALUES(Data[Simulation Number]),"ABCD",CALCULATE([E],CALCULATETABLE(VALUES(Data[Simulation Number])))),COUNTROWS(FILTER(Bins,[ABCD] >= Bins[Lower] && [ABCD] <= Bins[Upper])) >0))
4. For those interested in maintaining a continuos x-axis, I have created a new numeric column showing the midpoint of the bins (on the Bins table) and used that on the x-axis instead of bin names.
I had a need to use x-axis constant lines.
Here is my replication of the provided solution.You are welcome.
you cannot measure a measure.
Read about dynamic binning. Dynamic Banding or Grouping in Power BI – Using DAX Measures – Choose the Size of Bins - RADACAD
