How to sum by distinct values of different column

[slofish]

Hi everybody. This might be a simple calculation, but I'm sort of new to Power BI and need help.

I have a table like this, with a batch, part, and batch size. A batch might have several parts, but the Batch Size is always the same.

I'm trying to sum the total Batch Size, but I want the sum to only sum distinct values of Batch, something like this:

How can I calculate a measure with this total in Power BI?

Thanks!

[Anonymous · Accepted solution]

Hi @slofish 

You can create several measures as follow:

Measure = RIGHT(MAX([Part]), 2)

rank = RANKX(ALLEXCEPT('Table', 'Table'[Batch]), [Measure], , DESC, Dense)

min = MINX(ALLEXCEPT('Table', 'Table'[Batch]), [rank])

sum = CALCULATE(SUM('Table'[Batch Size]), FILTER('Table',[rank] = [min]))

Output:

Best Regards,
Yulia Xu

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