Forum Discussion
Event comparison
Hi all,
I've been stuggling throughout the weekend with this one, can't seem to make this work... Could you please help me out?
(please check sample data here)
I have a table with event data over the years. Each row also has a field containing the previous event ID, order number/date and Exhibitor ID:
I have 3 objectives:
1) Measure 1: distinctcount the Exhibitors that were in the previous event
(eg. event 6463 had 99 exhibitors; previous event is 6361 and had 118 exhibitors; here I can't figure out how to use the PreviousEventID as source for the distinctcount on a page that already has a filter to the current event id)
2) Measure 2: distinctcount of the Exhibitors that are on both current and previous event (comparing the 2 events)
3) Measure 3 -"Retention ratio": % of Exhibitors on both current and previous event vs. the count of Exhibitors on current event
(This should be quit easy if I have measure 2, of course)
I would grately appreciate support, going quit "nuts" on this 🙂
Hi, Anonymous
Please correct me if I wrongly understood your question.
When I compare the event ID 6464 and 6438, there is no common exhibitor's ID.
However, please check the below measures.
DistCount_Exhib_previous Event =VAR previousevent =MAX ( Table1[PreviousUBEventID] )VAR previousexhibitors =SUMMARIZE (FILTER (SUMMARIZE ( ALL ( Table1 ), Table1[EventID], Table1[ExhibitorID] ),Table1[EventID] = previousevent),Table1[ExhibitorID])RETURNCOUNTROWS ( previousexhibitors )DistCount_Exhib_Current and Previous Event =VAR currenteventexhibitor = VALUES(Table1[ExhibitorID])VAR previousevent =MAX ( Table1[PreviousUBEventID] )VAR previousexhibotors =SUMMARIZE (FILTER (SUMMARIZE ( ALL ( Table1 ), Table1[EventID], Table1[ExhibitorID] ),Table1[EventID] = previousevent),Table1[ExhibitorID])VAR result = COUNTROWS(INTERSECT( currenteventexhibitor, previousexhibotors))RETURNCOALESCE(result, 0)Retention Ratio =DIVIDE( [DistCount_Exhib_Current and Previous Event], [DistCount_Exhib_CurrentEvent])Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
2 Replies
Dear Jihwan_Kim ,
You are right, the the sample data did not contain "returning exhibitors" (I should have checked this, I'm sorry)
After changing the sample data, I got the desired results!!
Thank you very much!Hi, Anonymous
Please correct me if I wrongly understood your question.
When I compare the event ID 6464 and 6438, there is no common exhibitor's ID.
However, please check the below measures.
DistCount_Exhib_previous Event =VAR previousevent =MAX ( Table1[PreviousUBEventID] )VAR previousexhibitors =SUMMARIZE (FILTER (SUMMARIZE ( ALL ( Table1 ), Table1[EventID], Table1[ExhibitorID] ),Table1[EventID] = previousevent),Table1[ExhibitorID])RETURNCOUNTROWS ( previousexhibitors )DistCount_Exhib_Current and Previous Event =VAR currenteventexhibitor = VALUES(Table1[ExhibitorID])VAR previousevent =MAX ( Table1[PreviousUBEventID] )VAR previousexhibotors =SUMMARIZE (FILTER (SUMMARIZE ( ALL ( Table1 ), Table1[EventID], Table1[ExhibitorID] ),Table1[EventID] = previousevent),Table1[ExhibitorID])VAR result = COUNTROWS(INTERSECT( currenteventexhibitor, previousexhibotors))RETURNCOALESCE(result, 0)Retention Ratio =DIVIDE( [DistCount_Exhib_Current and Previous Event], [DistCount_Exhib_CurrentEvent])Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.