SUMX does not add up

[leo_89]

Hi there,

I calculating a value using a measure (let say the measure name is [event_value]). I have a column in the table which assigns a unique value to a half-hour block. I called that column Epoch Half Hour Number. I want to create a measure that sum-up all the [event-value] that occurred in the same Epoch Half Hour Number. I create measure as follow:

Sum same Epoch number =

var Time30Min = MAX(‘Table’[Epoch Half Hour Number])
RETURN
SUMX(FILTER(‘Table’,‘Table’[Epoch Half Hour Number]=Time30Min),
[event_value])

The above measure produces the following output:

The output does not sum up the event_value with the same Epoch Number. I have highlighted in red colour. Could anyone help me where am I making the mistake?

[AntrikshSharma]

@leo_89 Try something like this:

something =
VAR Time30Min =
    MAX ( 'Table'[Epoch Half Hour Number] )
RETURN
    CALCULATE (
        SUMX (
            'Table',
            'Table'[event_value]
        ),
        'Table'[Epoch Half Hour Number] = Time30Min,
        REMOVEFILTERS ( 'Table'[event_value] )
    )

[leo_89]

Thanks for your response. [event_value] is a measure, not table column. In that case, how should I use REMOVEFILTERS option

[AntrikshSharma]

@leo_89 It depends, without PBI file we will all keep guessing but it won’t be helpful for you.

On how to ask a technical question, if you really want an answer - SQLBI

[leo_89]

sample1.pbix (489.7 KB)

I have uploaded the file.

[Harsh]

Hello @leo_89,

Thank You for posting your query onto the Forum and for providing the working of the PBIX file.

Well, the result was not getting summed up because in the Table visual you’ve also added the field “EVENT_NO” which is distinct. So it actually split up the numbers based on the events. Below is the screenshot provided for the reference -

But if you remove the field “EVENT_NO” from the table visual it will show the consolidated results for field “Epoch Half Hour Number”. Below is the screenshot provided for the reference -

But since we want to show the “EVENT_NO” as well into the table visual and we also want to sum up/consolidate the figures based on “Epoch Half Hour Number”. Below is the formula provided for the reference that you need to use -

Sum same Epoch number - Harsh = 
CALCULATE( [Event_value] , 
    ALLEXCEPT( 'Table' , 
        'Table'[Epoch Half Hour Number] ) )

Now, based on this formula you’ll be able to see the results that you’re trying to achieve. Below is the screenshot of the results provided for the reference -

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

sample1 - Harsh.pbix (490.1 KB)