Forum Discussion
Advocate IISum 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] ) )
Advocate IIHi 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.
Community Champion
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
- 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
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/columnHASONEVALUE 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
- afk