topic Re: Count of event grouped by miles on automobile in DAX Commands and Tips

Count of event grouped by miles on automobile

sbuster — Mon, 04 Apr 2022 17:04:41 GMT

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

Re: Count of event grouped by miles on automobile

Anonymous — Mon, 04 Apr 2022 16:06:34 GMT

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

Re: Count of event grouped by miles on automobile

tamerj1 — Mon, 04 Apr 2022 17:03:06 GMT

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?

Re: Count of event grouped by miles on automobile

sbuster — Mon, 04 Apr 2022 17:05:19 GMT

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

Re: Count of event grouped by miles on automobile

tamerj1 — Mon, 04 Apr 2022 17:09:43 GMT

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?

Re: Count of event grouped by miles on automobile

sbuster — Mon, 04 Apr 2022 17:12:14 GMT

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

thanks

Re: Count of event grouped by miles on automobile

tamerj1 — Mon, 04 Apr 2022 17:52:44 GMT

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 ) )

Re: Count of event grouped by miles on automobile

Whitewater100 — Mon, 04 Apr 2022 18:47:06 GMT

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

Re: Count of event grouped by miles on automobile

v-xiaotang — Thu, 07 Apr 2022 06:49:52 GMT

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.

Re: Count of event grouped by miles on automobile

v-xiaotang — Thu, 28 Apr 2022 03:02:44 GMT

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.