Forum Discussion
Sum unique values daily
HI afk
Try this MEASURE
Measure = SUMX ( ALLSELECTED ( Table1[Date] ), CALCULATE ( FIRSTNONBLANK ( Table1[Visits], 1 ) ) )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] ) )
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
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
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] ) )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!
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?
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] ) )
@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!Attaching the correct link: https://drive.google.com/file/d/1b6x1Xm7ETijz6-Z7IJLsfBY5VEu76eNs/view?usp=sharing
Anyone, who could help please?
Appreciate it, Thanks!