Normalize multiple values to show relative comparison

[JJH_]

I am analyzing bicycle trip data that categorizes each user as a casual rider or a member rider.

95% of the data I am working with is about the characteristics of member riders and 5% is about casual riders. 

I am looking to create a relative comparison graph to show in which hour of the day bicycle riders start their trip, based on user type (casual vs member).

Given that the data that I am working with is heavily focused on member riders, if I use 'count number of rides' on a user type level, the Casual member ridership data just looks like a flat line because I don't have much data on casual riders.

However, if I show just the casual rider's data, we see that it is definitely not a flat line:

How can I normalize the data for member & casual riders so that I can show a relative distribution of when riders are starting their trip?

[jppv20]

Hi @JJH_ ,

An option would be to create two separate measures, one for the nr of rides for members and one for the nr of rides for casuals. Then you can add one of the measures in the values and the other one in the secondary values. In the visual options it is possible to create a secondary Y-axis. This will allow for both measures to show the distribution.

Hope this helps!

Jori

If I answered your question, please mark it as a solution to help other members find it more quickly.

Connect on Linkedin

[JJH_]

Hi @jppv20 

I tried creating 2 unique measures as you mentioned but I am not able to drag either values into the 'secondary value' field. Also, it sounds like your approach will lead to the same result if we are relying on 'count' as the aggregation function.

I think I need to create an aggregation is based on % of when riders are, for casual & member riders, then use the % of total ridership to 'normalize' the values for the graph.

[v-kelly-msft · Accepted solution]

Hi  @JJH_ ,

Create 2 measures as below:

casual = CALCULATE(SUM('Table'[Total number]),FILTER('Table','Table'[usertype]="Casual"))

member = CALCULATE(SUM('Table'[Total number]),FILTER('Table','Table'[usertype]="Member"))

Then put them in the fields as below:

And you will see:

Thus they will have separate Y axis and will show actual line shape.

For the sample.pbix file,pls see attached.(In page 2,you will see the same visual as you tested before,you could make a comparison between the 2 pages)

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[JJH_]

Thanks Kelly.

I was trying to avoid having to create separate tables just to count # of trip start/end time but I followed your approach and was able to create the visualization.