Forum Discussion
Sum unique values daily
Hi,
I would like to sum the number of visits to a URL, based on unique URLs for each day. An example of the data is as follows.
Date | URL | Product | Visits |
01/03/2018 | Shoes | 500 | |
01/03/2018 | Clothes | 500 | |
01/03/2018 | Shoes | 500 | |
|
|
|
|
02/03/2018 | Shoes | 600 | |
02/03/2018 | Clothes | 600 | |
02/03/2018 | Shoes | 600 |
The "issue" here, is that the same URL can appear on multiple rows if there are several products related to that URL, and therefore the page visits is essentially duplicated for that URL.
Currently, my data is showing the following:
Total visits for URL1 = 2200
Total visits for URL2 = 1100
This is however incorrect as both URLs should have the same number of visits (1100), but URL1 appeared on multiple rows as there were multiple products related to that URL.
Is there a way to count the visits per unique URL for each day?
Would really appreciate any help.
Thank you!
afk
HI afk
Try this MEASURE
Measure = SUMX ( ALLSELECTED ( Table1[Date] ), CALCULATE ( FIRSTNONBLANK ( Table1[Visits], 1 ) ) )To get figures with correct total...please create another MEASURE which will reference the first MEASURE i.e.
Measure 2 = IF ( HASONEFILTER ( Table1[URL] ), [Measure], SUMX ( ALLSELECTED ( Table1[URL] ), [Measure] ) )
18 Replies
HI afk
Try this MEASURE
Measure = SUMX ( ALLSELECTED ( Table1[Date] ), CALCULATE ( FIRSTNONBLANK ( Table1[Visits], 1 ) ) )Hi Zubair_Muhammad,
Thanks for your suggestion. However, it didn't seem to work.
Can you explain the logic behind the formula?
Retrieving the first value in the column (FIRSTNONBLANK) does seem to be a possible solution. But I need to retrieve the first value based on a value in another column. Is this possible?
Thanks!
afk
