Bin the Results of a Measure

DonRitchie — Wed, 12 Apr 2023 12:00:46 GMT

I have a very simple table that lists the distance travelled, fuel used, by vehicle and date per row.

Vehicle	Distance	Gallons	Date
Vehicle 1	200	50	4/12/2023
Vehicle 1	300	60	4/13/2023
Vehicle 2	250	45	4/12/2023
Vehicle 2	200	40	4/13/2023

I have a measure MPG = sum([Distance])/sum([Gallons])

There are hundreds of vehicles in this table across years of data. I want the user to be able to search a specific date range and see the distinctcount of vehicles that fit into a "bin", but I can't bin a measure.

I can make a scattergram that shows the MPG per vehicle:

But this is unwieldy considering the number of vehicles.

I tried using Switch to create groups:

MPG Group = switch(TRUE(),[MPG]<4,"< 4 MPG", [MPG]<6, "4-6 MPG", [MPG]<6.5, "6-6.5 MPG", [MPG]<7, "6.5-7 MPG", "7+ MPG")

But I can't get the Switch Measure to show up in any visualizations other than a Matrix:

Ultimately, what I want is a Scattergram or a Proportional Box that shows each MPG "Bin" and the size and tooltip displays the distinctcount of Vehicles that fall into that bin.

Any ideas? This should be easier than it is.

Thanks!

Re: Bin the Results of a Measure

ppm1 — Wed, 12 Apr 2023 12:30:59 GMT

Please see this article for how to do a dynamic segmentation like this.

Dynamic segmentation – DAX Patterns

Pat

topic Re: Bin the Results of a Measure in DAX Commands and Tips

Bin the Results of a Measure

Re: Bin the Results of a Measure