Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Johandp07
Regular Visitor

Calculating the total of only distinct numbers in a row

Good day community, I am a newbie to DAX and I am stuck with what seems to be a simple calculation in Power-BI...

 

I have 5 Columns that calculate the number of Chickens used per product and need to total them in a new column. However, I only want to add distinct values as 1 chicken can be used to make up multiple products.

 

Example;

Johandp07_0-1695114578428.png

I need the total of distinct values from all 5 columns in the new Column at the end. (Only 48 + 96, 15 + 7.5 and 106.5 + 213) But I still need to sum all the columns as some products consume multiple chickens.

 

Hope this makes sense?

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

Hi @Johandp07 ,

Please try to create calculated column with below dax formula:

Sum for distinct =
VAR _a = [Column1]
VAR _b = [Column2]
VAR _c = [Column3]
VAR _d = [Column4]
VAR _e = [Column5]
VAR tmp = { _a, _b, _c, _d, _e }
VAR tmp1 =
    DISTINCT ( tmp )
RETURN
    SUMX ( tmp1, [Value] )
Sum for all =
VAR _a = [Column1]
VAR _b = [Column2]
VAR _c = [Column3]
VAR _d = [Column4]
VAR _e = [Column5]
VAR tmp = { _a, _b, _c, _d, _e }
RETURN
    SUMX ( tmp, [Value] )

vbinbinyumsft_0-1695362663919.png

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-binbinyu-msft
Community Support
Community Support

Hi @Johandp07 ,

Please try to create calculated column with below dax formula:

Sum for distinct =
VAR _a = [Column1]
VAR _b = [Column2]
VAR _c = [Column3]
VAR _d = [Column4]
VAR _e = [Column5]
VAR tmp = { _a, _b, _c, _d, _e }
VAR tmp1 =
    DISTINCT ( tmp )
RETURN
    SUMX ( tmp1, [Value] )
Sum for all =
VAR _a = [Column1]
VAR _b = [Column2]
VAR _c = [Column3]
VAR _d = [Column4]
VAR _e = [Column5]
VAR tmp = { _a, _b, _c, _d, _e }
RETURN
    SUMX ( tmp, [Value] )

vbinbinyumsft_0-1695362663919.png

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.