cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## 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.url1.com Shoes 500 01/03/2018 www.url1.com Clothes 500 01/03/2018 www.url2.com Shoes 500 02/03/2018 www.url1.com Shoes 600 02/03/2018 www.url1.com Clothes 600 02/03/2018 www.url2.com 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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

## Re: Sum unique values daily

HI @afk

Try this MEASURE

```Measure =
SUMX (
ALLSELECTED ( Table1[Date] ),
CALCULATE ( FIRSTNONBLANK ( Table1[Visits], 1 ) )
)
```
Super User

## Re: Sum unique values daily

@afk

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] )
)
```
15 REPLIES 15
Super User

## Re: Sum unique values daily

HI @afk

Try this MEASURE

```Measure =
SUMX (
ALLSELECTED ( Table1[Date] ),
CALCULATE ( FIRSTNONBLANK ( Table1[Visits], 1 ) )
)
```
Regular Visitor

## Re: Sum unique values daily

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

Super User

## Re: Sum unique values daily

HI @afk

It works when I use your sample data.

See the pic below and file attached

Super User

## Re: Sum unique values daily

@afk

Since you are saying that page visits are duplicated across rows, I take any one value for a particular Date for each ID.

And then sum this value across all dates

You can take MAX, MIN or average as well....if the VALUES are same across products for one ID on each date

Regular Visitor

## Re: Sum unique values daily

Thanks for the explanation. Whilst the count fo URL1 is correct, the grand total should be 2200 (1100 + 1100).

Can this be done?

Apologies for any confusion.

Super User

## Re: Sum unique values daily

@afk

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] )
)
```
Super User

## Re: Sum unique values daily

@afk

See the revised file attached

Regular Visitor

## Re: Sum unique values daily

This seems to have worked. Could you explain the syntaxes in the formula and how we arrive to the figures?

Just so I understand how it works rather than just copying+pasting the formula.

Many thanks,

afk

Super User

## Re: Sum unique values daily

@afk

It is a quite common situation to have a wrong Total for a MEASURE in rows and/ or columns

For example see Greg's post

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/m-p/63376

So we use different formulas for individual rows/columns and total row/column

HASONEVALUE or HASONEFILTER functions help detect if a row is a TOTAL row or not.

We use SUMX to aggregate the measure (used for individual rows) over allrows of the table so that we can get the correct total in the Table Visual