Create single measure for frequency distribution comparison

[JJH_]

I am working with a trip data set that identifies when trips start & end. There are 2 users in scope: member & casual.

I am trying to create a distribution graph to show the differences in trip start/end behavior by comparing member vs casual users. 

Here is a sample of the data table & a pivot table to summarize the data characteristics. 

I am trying to create a calculated measure that I can use to compare casual vs member.

I tried using 'count rows' to aggregate ride data, but this does not work because I do not have equal # of data points for both casual & member rides. 

I think I need to compute % of total rides done to identify in which hour people are riding in a day and then visualize this on a line graph. Any suggestions?

I tried asking the same question in a different post but wasn't able to resolve it: https://community.powerbi.com/t5/Desktop/Normalize-multiple-values-to-show-relative-comparison/m-p/2...

[v-chenwuz-msft · Accepted solution]

Hi @JJH_ 

If possible, until then you can provide pbix files, which do not contain sensitive data. Or a table rather than a screenshot.

I have created some aggregated data based on your table. to show the method I am using.

When not using measure it shows similar results to the visual you have in another post.

And try this measure:

compare =
VAR _member =
    SUM( 'Table'[Continue T rows] ) * 0.05
VAR _casual =
    SUM( 'Table'[Continue T rows] ) * 0.95
RETURN
    IF( MAX( 'Table'[type] ) = "member", _member, _casual )

And the result:

If you want to create a table to calculate the number of people appearing over a period of time based on a time duration table, this is a post you can refer to.

https://community.powerbi.com/t5/Desktop/Distribution-of-Data-over-24-Hours-amp-Line-Chart/td-p/2073...

I put my pbix file in the attional you can reference.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@JJH_ 

Since you have high and very low numbers to compare for absolute values, % of the total is better. 
Create 2 measures, % of total hours for each hour for Casual and Member. Then, you can visualize the % on a column or line chart