cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors