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

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's avatar
Anonymous
Not applicable
6 years ago

@Zubair_MuhammadZubair_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!