Sum of values based on distinct values in another column

[JRB]

Hi All,

Have searched all through the forums but I cant manage to crack this one.

I'm attempting to sum the total of 'Container'[HBLperConsol] for each distinct 'Container'[ConsolNum] filtered by the dates below.

The date filter is working, but the measure is returning the sum for every row that matches the date filter, as the 'Container'[ConsolNum] column contains duplicates.

DAX measure below:

CurrentWkHBL =
VAR CurrentYear =
    YEAR( TODAY() )
VAR CurrentWeek =
    WEEKNUM( TODAY(), 2 )
RETURN
    SUMX(
        DISTINCT( Container[ConsolNum] ),
        CALCULATE(
            SUM( Container[HBLperConsol] ),
            YEAR( Container[ETA] ) = CurrentYear
                && WEEKNUM( Container[ETA], 2 ) = CurrentWeek
        )
    )

Any guidance would be greatly appreciated 🙂 

[Jihwan_Kim · Accepted solution]

Hi, @JRB 

Please check the below picture and the measure.

If you want to stick to your measure, please try to replace sum with max, like below.

CurrentWkHBL =

VAR CurrentYear =

YEAR( TODAY() )

VAR CurrentWeek =

WEEKNUM( TODAY(), 2 )

RETURN

SUMX(

DISTINCT( Container[ConsolNum] ),

CALCULATE(

MAX( Container[HBLperConsol] ),

YEAR( Container[ETA] ) = CurrentYear

&& WEEKNUM( Container[ETA], 2 ) = CurrentWeek

)

)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[JRB]

Thank you @Jihwan_Kim

This solution works perfectly.

I'm now off to study the MAX vs SUM functions.

Regards,

Jordan

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.

[JRB]

Hi Ashish,

Below some sample data:

The expected result for Asia should be 33 (12 + 7 + 14 - the sum HBLperConsol for each distinct value in Consol Num) But the measure is returning 12 + 12 + 7 + 14 - the sum of every value in HBLperConsol.

Hope this clarifies

[Ashish_Mathur]

Hi,

In the Query Editor, please ensure that HBLperConsol is a numeric column.  Try this measure

=SUMX(VALUES(HBLperConsol),HBLperConsol)

Hope this helps.

[JRB]

@amitchandak I hear you're an expert, can you help 😀