cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JJH_
Frequent Visitor

Normalize multiple values to show relative comparison

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.tempsnip.png

 

Capture.PNG

 

 

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

 

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?

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

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:

vkellymsft_0-1633944420039.png

And you will see:

vkellymsft_3-1633944731315.png

 

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!

 

View solution in original post

4 REPLIES 4
jppv20
Solution Sage
Solution Sage

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_
Frequent Visitor

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.

 

tempsnip.png

v-kelly-msft
Community Support
Community Support

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:

vkellymsft_0-1633944420039.png

And you will see:

vkellymsft_3-1633944731315.png

 

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!

 

View solution in original post

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.

 

snip1.PNGsnip2.PNG

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors