DAX Formula Help

[seanpratt]

I'm not sure how to phrase this, but I'm simply looking to sum up the story points by each Work Item ID. When I aggregate all of our story points for a sprint in Power BI, it's saying ID 21822 is worth 15 points, and then 22216 is worth 15 points when really it should be 5 each.

I've got it in 3 rows here to allow for the tags to play a larger role in our reporting.

What's the formula I need here?

[Anonymous]

Hi @seanpratt ,
Based on your description, the data you are displaying is duplicated across columns, which results in automatic aggregation when displayed. If you want to display only the values of each item, unaggregate them in the visual object

Or you can create a measure

Story Points_ = 
CALCULATE(
    AVERAGE('Table'[Story Points]),
    ALLEXCEPT(
        'Table',
        'Table'[Work Item ID]
    )
)

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[vicky_]

You could try to aggregate by MIN or AVERGE instead of SUM. However, since it looks like most rows except the Tags are the same, I would try and fix the structure of your data - the first table would be something like the Work Item Table, which would have the Work Item Id, dates, and Story Points, etc without duplicates. And then you might have a Work Item Tag table (which just has a work Item ID and Tags), which would have a M:1 relationship with the Work Item Table. That way, you won't have to deal with the multiple story points per work item.