cancel
Showing results for
Did you mean:

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

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;

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
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] )
``````

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.

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] )
``````

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors