cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JRB
Frequent Visitor

Sum of values based on distinct values in another column

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 🙂 

1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

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
)
)
 
Picture4.png
 

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


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

6 REPLIES 6
Jihwan_Kim
Community Champion
Community Champion

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
)
)
 
Picture4.png
 

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


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

JRB
Frequent Visitor

Thank you @Jihwan_Kim

 

This solution works perfectly.

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

 

Regards,

Jordan

Ashish_Mathur
Super User III
Super User III

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

Below some sample data:

JRB_1-1620095460699.png

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

Hi,

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

=SUMX(VALUES(HBLperConsol),HBLperConsol)

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
JRB
Frequent Visitor

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors