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

Create single measure for frequency distribution comparison

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.data.PNG 

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

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

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.

vchenwuzmsft_0-1634028254522.png

 

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:

vchenwuzmsft_1-1634028254526.png

 

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.

View solution in original post

2 REPLIES 2
v-chenwuz-msft
Community Support
Community Support

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.

vchenwuzmsft_0-1634028254522.png

 

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:

vchenwuzmsft_1-1634028254526.png

 

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.

View solution in original post

Fowmy
Super User
Super User

@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


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

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!

Top Solution Authors
Top Kudoed Authors