Histogram based on measure values

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

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. 

[Ashish_Mathur]

You are welcome.

[lbendlin]

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