how to sum value and splitting result into group to display in chart

[Anonymous]

Hello Everyone,

 I would like to create bar chart to display group of sum distance (Axis X)   and Count of truck in each group (Axis Y)
(Sum distance is vary depend on date range slicer  ex. single day  or  2-3 days)
(the result of sum distance of each truck is splitted in to group)

the result should be like this ( i made this in excel I dont know to to create visualize like this in BI)

example of slicer in BI

I have attach my Sample data in pbix file 

Sample pbix file 


please advice  Thank you very much.

Best Regards

JJ

[Anonymous]

Hi  @Anonymous ,

Here are the steps you can follow：
1. Enter data – create a table.

2. Create calculated table.

Flag =
 CROSSJOIN(
     'Table',
     DISTINCT('Sheet1'[Engine]))

3. Create calculated column.

Countof stuck =
SWITCH(
    TRUE(),
    'Sheet1'[Distance] <1000,"below 1000",
    'Sheet1'[Distance]  >=1000&&'Sheet1'[Distance] <2000,"1000",
    'Sheet1'[Distance]  >=2000&&'Sheet1'[Distance] <3000,"2000",
    'Sheet1'[Distance]  >=3000&&'Sheet1'[Distance] <4000,"3000",
    'Sheet1'[Distance]  >=4000&&'Sheet1'[Distance] <5000,"4000",
    'Sheet1'[Distance]  >=5000&&'Sheet1'[Distance] <6000,"5000",
    'Sheet1'[Distance]  >=6000,"6000 or more")

4. Create measure.

Flag_Meausre =
COUNTX(
    FILTER(ALLSELECTED(Sheet1),
    'Sheet1'[Engine]=MAX('Flag'[Engine])&&'Sheet1'[Countof stuck]=MAX('Flag'[Group])),[Countof stuck])

5. Result:

Best Regards,

Liu Yang

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

[Anonymous]

Hello @Anonymous,

 As I check the result file you did  but the result in incorrect. number show on Axis Y should be distinct count and total value should not exceed 5  as the number of truck in data are  5 trucks in total.  from your visualization show total is 15 which is incorrect.

Please recheck again  and Thank you very much for your effort.

Best Regards 

jj