Count of event grouped by miles on automobile

[sbuster]

I have a table with an event + miles for a fleet of automobiles.  I would like to calculate the total number of events over different bins of miles.  Basically trying to determine how many events occur given a range of miles.  The result would be:

miles bin          count of events    auto id

----------          -----------------    --------

0-1K                1                          1

1K-10K            2                         1

...

where the data I have is as follows:

mile occurred     auto id    event id

---------------      --------   ---------

591                      1            A

13000                  1           B

14000                  1           C

Thanks

[v-xiaotang · Accepted solution]

Hi @sbuster 

If user will most likely be looking at 1 automobile at a time then you can try this, just need to create an auxiliary table and a column,

(1) create an auxiliary table

miles bin = DIVIDE('miles bin'[start],1000)&"K-"&DIVIDE('miles bin'[end],1000)&"K"

(2) create a column

miles bin = 
var _v= 'Table'[mile occurred]
var _range= CALCULATE(MAX('miles bin'[miles bin]),FILTER(ALL('miles bin'),'miles bin'[start]<=_v && 'miles bin'[end]>_v))
return _range

result

Best Regards,

Community Support Team _Tang

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

[v-xiaotang]

Hi @sbuster 

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

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

[Whitewater100]

Hi:

There are three ways to group/bin. This report includes two of them (Power Query has this feature as well).

Here is a link to an example file. I hope this helps! You can also change the bin sizes.

https://drive.google.com/file/d/1OA3NbOHCOXzVJiABNqHQggLrWR8sjtqR/view?usp=sharing 

[tamerj1]

hi @sbuster 

I can see that for the same automobile same event you have multiple miles. How are we supposed to deal with that? Shall we consider the sum or the maximim value. The bins shall be based on each event or as overall?

[sbuster]

My apologies, mistyped it as each should have different event id.  fixed the example.

[tamerj1]

@sbuster 

One more question. In you real data there would be multiple automobiles in the same mile bin. Would you like have them all indicated in your report?

[sbuster]

yes, there are multiple autos in real data set all.  User will most likely be looking at 1 automobile at a time.

thanks

[tamerj1]

@sbuster 

Start with creating a new column

Mile Bins =
SWITCH (
    TRUE (),
    Data[mile occurred] > 0
        && Data[mile occurred] < 1000, "0 - 1k",
    Data[mile occurred] >= 1000
        && Data[mile occurred] < 10000, "1 - 10k"
)

Use this column in you matrix visual. 
the count measure

count of events =
DISTINCTCOUNT ( Dat[auto id] )

And the id's measure

Auto Id's =
CONCATENATEX ( VALUES ( Data[auto id] ), Data[auto id], UNICHAR ( 10 ) )

[SamB7]

Create a calculated column with and if/switch statement to calculate the range (if range < 1000  then "0 - 1K"....)

You can then create a count measure for the events to get the results needed by range