Forum Discussion

JJH_'s avatar
JJH_
Frequent Visitor
4 years ago
Solved

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.

 

 

 

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?

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    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!

     

4 Replies

  • jppv20's avatar
    jppv20
    Solution Sage
    4 years ago

    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_'s avatar
      JJH_
      Frequent Visitor
      4 years ago

      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's avatar
        v-kelly-msft
        Community Support
        4 years ago

        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!