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!

     

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.