Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
afk
Advocate II
Advocate II

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
Zubair_Muhammad
Community Champion
Community Champion

HI @afk

 

Try this MEASURE

 

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

Regards
Zubair

Please try my custom visuals

View solution in original post

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

Regards
Zubair

Please try my custom visuals

View solution in original post

18 REPLIES 18
Zubair_Muhammad
Community Champion
Community Champion

HI @afk

 

Try this MEASURE

 

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

Regards
Zubair

Please try my custom visuals

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

HI @afk

 

It works when I use your sample data.

See the pic below and file attached

 

sumunique.png


Regards
Zubair

Please try my custom visuals

@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


Regards
Zubair

Please try my custom visuals

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.

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

Regards
Zubair

Please try my custom visuals

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

@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


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

@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
Not applicable
Anonymous
Not applicable

Anyone, who could help please?

 

Appreciate it, Thanks!

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.

 

 URLJob roleCompanyProductVisits
URL1JuniorAShoes300
URL1ExecAShoes400
URL1ManagerAShoes500
URL1TechnicalAShoes200
     
URL1JuniorAClothes300
URL1ExecAClothes400
URL1ManagerAClothes500
URL1TechnicalAClothes200

 

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!

@afk

 

I will look into it and get back to you


Regards
Zubair

Please try my custom visuals

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

 


Regards
Zubair

Please try my custom visuals

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. 

 

 URLJob roleCompanyProductVisits
URL1JuniorAShoes300
URL1ExecAShoes400
URL1ManagerAShoes500
URL1TechnicalAShoes200
URL1JuniorAClothes300
URL1ExecAClothes400
URL1ManagerAClothes500
URL1TechnicalAClothes200
URL1JuniorBClothes100
URL1ExecBClothes200
URL1JuniorBShoes100
URL1ExecBShoes200

 

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?

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

Regards
Zubair

Please try my custom visuals

Great!

 

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

 

🙂

@afk

 

See the revised file attached

 

sumunique2.png

 

 


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.