How to use distinct and get the unique values in created measure (expression) by account name?

[dinesharivalaga]

Hi Experts ,

I am stuck with the scenario below :

Recently i have applied unpivot columns for few columns to rearrange the rows & attributes.

After that most of the columns are duplicated (expected) , I have fixed few values related to that columns by using DISTINCT dax , but I cannot use the same DISTINCT into the measure which i have created to summing up the values and with using some other measures as below :

Revenue & Margin = SUMX('Delivery Updates',[Revenue*Margin]) --> [Revenue*Margin] is an another measure which result is OK.

The result should be : 51246 euro (1 Account)

But once i did unpivot columns 1 row of account was duplicated as 27 rows with same account name as below :

before unpivot :

after unpivot : (27 rows)

Now the case is I cannot use DISTINCT to get the 1 Account value from this measure.

Now it is showing below huge number : total 27 rows = 27*51246

Please help to fix this part asap 😞

Due to this the total margin% is impacted ..

Thanks

DK

[v-zhangtin-msft · Accepted solution]

Hi, @dinesharivalaga 

As an example, does your sample data look like this?

Maybe you can try this formula.

Measure = 
 VAR _table=DISTINCT('Table')
 RETURN
 SUMX(_table,[Value])

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.