Forum Discussion

prl's avatar
prl
Microsoft Employee
7 years ago

Incorrect Sum of Calculated Measure

I have date slicer on dashboard from which I can select a specific date or range date. When I select date range using slicer, I want to display total of calculated measure for selected date in card visual. But the result of sum of calculated measure doesn’t match.

Datetest4
10/31/2018 0:00260
11/1/2018 0:00251
10/29/2018 0:00145
10/30/2018 0:00142
11/2/2018 0:00140

The Total is displayed as 1335.

If I add these values, the value should be 938 instead of 1335.

Calculated Measure test4 is calculated as:

test4 =

VAR mindate = Min('Calculation Table'[StartOf8Weeks])

VAR maxdate = MAX('Calculation Table'[EndOf8Weeks])

VAR val = Calculate(SUM(SLTLines[TestCount]),FILTER(ALL(SLTLines), (SLTLines[TestReleaseDate]>=mindate)&&(SLTLines[TestReleaseDate]<=maxdate)))

return

SUMX('Calculation Table',val)

 

 

I am not able to figure out why I am getting incorrect sum. Can anyone direct me to solve this?

 

Thanks in advance!

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    What is the purpose of the SUMX line in your measure?  Wouldn't you simply want your measure to be

    test4 =
VAR mindate = Min('Calculation Table'[StartOf8Weeks])
VAR maxdate = MAX('Calculation Table'[EndOf8Weeks])
VAR val = Calculate(SUM(SLTLines[TestCount]),FILTER(ALL(SLTLines), (SLTLines[TestReleaseDate]>=mindate)&&(SLTLines[TestReleaseDate]<=maxdate)))
RETURN
val
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    Create a Calendar Table and build a relationship from the Date column of the SLTLines Table to the Date column of the Calendar Table.  Drag the Date column from the Calendar Table into the slicer.  Now write this measure

     

    =SUM(SLTLines[TestCount])

     

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI prl,

     

    You can also take a look at following link which told about how to deal with measure calculation on total level:
    Measure Totals, The Final Word

     

    Regards,

    Xiaoxin Sheng