I have 2 tables: L+7 Table & L+SD Table
Both tables have the same columns and unique values for Data Stream as L+7 table has L+7 under Data Stream and L+SD table as L+SD under Data Stream.
These are the columns:
Data Stream | Dates | Daypart | Viewing Source | Time | Demo | IMP
I have two objectives, one was to create visuals with filter options for Data Stream, Viewing Source and Demo. The other was to be able to deliver DVR lift for whatever date was selected.
What I have done:
I appended L+7 table with the L+SD table so that I could have visuals with the filter options for Data Stream, Viewing Source and Demo. This worked! I named this new table Prime. In relationship mode, Prime is connected to the L+7 Table and L+SD Table (and both are connected to the dates table and demo sort table for sorting the demos so they appear in the appropriate order).
What I did next was merge L+7 Table and L+SD Table into a table names Prime Merge and created a calculated column in the query editor that calculated for DVR Lift which is L+7 IMP – L+SD IMP. Then in report mode, I created 2 more columns, DVR%ofTotal = Divide (DVR Lift, L+7 IMP) and %LIFT = Divide (DVR Lift, L+SD IMP). This seemed to work… until I noticed that the visual filters from the Prime table (Viewing Source and Data Stream) would not work with the Card visualizations. Data Stream I did not expect to work since the Prime Merge table would not recognize it but the Viewing Source filter should have. When I tried to make a relationship exist between Prime and Prime Merge, Power BI said it could not create a relationship between the tables because it would create ambiguity in the other relationships. I thought it was an issue with how I merged the tables so I tried the merge again and named this table Merge 1. Not only did I have the same problem as before but the data looked different this time! I'm not sure which data is correct but I take this all to mean merging the tables was not the proper solution.
What I want to do:
After some reading I think what I should have done is create a calculated table from L+7 table and the L+SD table? Or maybe have a calculated column on the Prime table that filters for the Data source so that I am able to calculate DVR lift and then the other 2 subsequent columns? Honestly, I am not sure what the best course of action is. I've been struggling with this for days.
These are the formulas
DVR Lift = L+7 [IMP] – L+SD [IMP]
%Lift = DVR Lift / L+SD [IMP]
DVR%OFTOTAL = DVR Lift / L+7 [IMP]
I am at a complete loss. Any and all help is greatly appreciated! Thank you in advance for your time.
