Calculating the total of only distinct numbers in a row

[Johandp07]

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?

[v-binbinyu-msft · Accepted solution]

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.