Sum unique values daily

[afk]

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

[Zubair_Muhammad · Accepted solution]

HI @afk

Try this MEASURE

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

[Zubair_Muhammad · Accepted solution]

HI @afk

Try this MEASURE

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

[afk]

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

[Zubair_Muhammad]

HI @afk

It works when I use your sample data.

See the pic below and file attached

[Zubair_Muhammad]

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

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.

[Zubair_Muhammad · Accepted solution]

@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] )
)

[afk]

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

[Zubair_Muhammad]

@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

[Anonymous]

@Zubair_Muhammad@Zubair_Muhammad 

I followed the same formula you've mentioned from this section, however my total seems to be still incorrect.

The total should be 94.35 instead of 75.79.

Link  https://drive.google.com/file/d/1jt6kZLgS9KAGaricTLnpGuuTs93N5sDU/view?usp=sharing

Formula I used:

Checking = Sumx(ALLSELECTED(billing_to_pay_btp[date]),CALCULATE(FIRSTNONBLANK(billing_to_pay_btp[timewarp_time_duration],1)))

Checking2 = (IF(HASONEFILTER(billing_to_pay_btp[employee_id]),[Checking],

Sumx(ALLSELECTED(billing_to_pay_btp[employee_id]),[Checking])))-Sum(billing_to_pay_btp[lunch_duration_tw])

Looking forward for your immediate response please. thank you!

[Anonymous]

@Zubair_Muhammad 

Attaching the correct link: https://drive.google.com/file/d/1b6x1Xm7ETijz6-Z7IJLsfBY5VEu76eNs/view?usp=sharing

[Anonymous]

Anyone, who could help please?

Appreciate it, Thanks!

[afk]

Hi @Zubair_Muhammad,

There is a slight alteration to the data but I haven't been able to calculate the totals correctly. I've added the columns job role and company to see how many times a person has visited the site.

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

The issue here is that the URL has 2 products assigned to it and the number of visits gets duplicated. Note: if there are multiple products, the values in job role, company and visits will always be the same.

The correct number of visits for URL1 should be 1400 (300+400+500+200) but my data is showing 2800.

Similarly, calculating the number of visits per job role and company size also get duplicated.

Please can you help?

Many thanks again!

[Zubair_Muhammad]

@afk

I will look into it and get back to you

[Zubair_Muhammad]

@afk

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

[afk]

Hi @Zubair_Muhammad

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?

[Zubair_Muhammad]

@afk

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

[afk]

Great!

Thanks a lot @Zubair_Muhammad. You've been amazing help.

🙂

[Zubair_Muhammad]

@afk

See the revised file attached