Data blending summation issue
B.G P May 10, 2016 1:34 PMI am facing one issue while aggregating the data from date to month rollup on data blending of two different sources. When I am rolling up data which is % based from date level to month level, It is not aggregating based on date at month level. See below requirement to get clear idea.
Month of Date opened, Day of Date Opened, Full/Partial & Percent impacted user are coming from EXCEL data source (Primary Data source). The Total User is coming from Google Analytic (Secondary Data Source). Whereas “User impacted by percent” is the calculated field created based on these data source.
Here is the formula of User impacted by percent:
if SUM([Percent impacted])=1
THEN SUM([GA (GA_Dataset)].[User])
ELSE (SUM([GA (GA_Dataset)].[User])*SUM([Percent impacted]))
END
I tired ATTR in above formula instated of SUM(Percent impact) but didn’t help me. See attached workbook sample for detailed understanding.
Data at date & month level:
Month of Date Opened | Day of Date Opened | Full/Partial | Percent impacted User | User impacted by percent | Total user |
Feb-16 | 05-Feb-16 | Partial | 1% | 51 | 5194 |
15-Feb-16 | Partial | 50% | 1,802 | 3604 | |
Mar-16 | 03-Mar-16 | Partial | 1% | 69 | 6953 |
23-Mar-16 | Full | 100% | 6,049 | 6094 |
Expected result
Month of Date Opened | Full/Partial | Percent impact | User impacted by percent | Total User |
Feb-16 | Partial | 51% | 1853 | 8798 |
Mar-16 | Partial | 1% | 69 | 6953 |
Full | 100% | 6,049 | 6094 |
This is what I am getting after removing date dimension from worksheet. The “User impacted by percent” is doing sum first and then do % on top of that data and showing here instead of above mentioned dates of Feb.
Month of Date Opened | Full/Partial | Percent impact | User impacted by percent | Total User |
Feb-16 | Partial | 51% | 4486 | 8798 |
Mar-16 | Partial | 1% | 69 | 6953 |
Full | 100% | 6,049 | 6094 |
Thanks in advance..
-
Outage Vs GA Analysis.twbx 29.6 KB