Forum Discussion

afk's avatar
afk
Advocate II
8 years ago
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...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    HI afk

     

    Try this MEASURE

     

    Measure =
SUMX (
    ALLSELECTED ( Table1[Date] ),
    CALCULATE ( FIRSTNONBLANK ( Table1[Visits], 1 ) )
)
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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's avatar
afk
Advocate II
8 years ago

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's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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's avatar
      afk
      Advocate II
      8 years ago

      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's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

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