Forum Discussion
Solved
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 www.url...
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] ) )
Sorry for late reply
Please try these MEASURES
Measure = SUMX ( VALUES ( Table1[Visits] ), CALCULATE ( VALUES ( Table1[Visits] ) ) )
Measure 2 =
IF (
HASONEFILTER ( Table1[URL] ),
[Measure],
SUMX ( ALLSELECTED ( Table1[URL] ), [Measure] )
)
Thanks for the suggestion again! This has worked perfectly with the current dataset.
If however I add another company to the data, the total gets thrown off.
URL Job role Company Product Visits URL1 Junior A Shoes 300 URL1 Exec A Shoes 400 URL1 Manager A Shoes 500 URL1 Technical A Shoes 200 URL1 Junior A Clothes 300 URL1 Exec A Clothes 400 URL1 Manager A Clothes 500 URL1 Technical A Clothes 200 URL1 Junior B Clothes 100 URL1 Exec B Clothes 200 URL1 Junior B Shoes 100 URL1 Exec B Shoes 200 Using the formula, the total number of visits for URL1 comes up as 1500 when it should really be 1700.
The formula does however work when calculating visits per job role/company, just not for the URL.
Any ideas?
Ok. Try this one
Measure = SUMX ( SUMMARIZE ( Table1, Table1[Company], Table1[Visits] ), CALCULATE ( VALUES ( Table1[Visits] ) ) )Measure 2 = IF ( HASONEFILTER ( Table1[URL] ), [Measure], SUMX ( ALLSELECTED ( Table1[URL] ), [Measure] ) )
