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 casua...
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!

     

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!

     

    • JJH_'s avatar
      JJH_
      Frequent Visitor
      4 years ago

      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.