cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
afk Regular Visitor
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
Super User

Re: Sum unique values daily

HI @afk

 

Try this MEASURE

 

Measure =
SUMX (
    ALLSELECTED ( Table1[Date] ),
    CALCULATE ( FIRSTNONBLANK ( Table1[Visits], 1 ) )
)
Super User
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
Super User

Re: Sum unique values daily

HI @afk

 

Try this MEASURE

 

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

Re: Sum unique values daily

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

Super User
Super User

Re: Sum unique values daily

HI @afk

 

It works when I use your sample data.

See the pic below and file attached

 

sumunique.png

Super User
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

afk Regular Visitor
Regular Visitor

Re: Sum unique values daily

Hi @Zubair_Muhammad,

 

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
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
Super User

Re: Sum unique values daily

@afk

 

See the revised file attached

 

sumunique2.png

 

 

afk Regular Visitor
Regular Visitor

Re: Sum unique values daily

Thanks @Zubair_Muhammad !

 

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
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