# how to create a measure based on another two measures in DAX?

• ### Question

• I have a CalculatedColumn UniqueCount defined as

= distinctcount(tblA[id]) <-works.

I have a measure which is mySum:=sum(tblA[salesAmount]]) <- works.

I am trying to find the average sales per id and in Measure Pane, I've got , AvgSales:=mySum/UniqueCount and #error and says, semantic error: the value for UniqueCount cannot be determined.  Either UniqueCount  doesn't exist or there is no current row with column, UniqueCount.

how to create a measure based on a calc column and measure?

-- IBM has jobs for USA employee to work oversea (India) and get their wages.

Monday, September 16, 2013 9:04 PM

• Hello,

I tested it and for me it works; you can create measures which bases on other measures of the same table.

Example:

```SumA:=Sum(Tab1[ValueA])
SumB:=Sum(Tab1[ValueB])
DistinctCnt:=DISTINCTCOUNT(Tab1[ItemCode])

RateDstA:=Tab1[SumA] / Tab1[DistinctCnt]
RateDstB:=Tab1[SumB] / Tab1[DistinctCnt]```
In PowerPivot:

There seems to be only a bug, when you use German regional settings; RateDstB should be 0.975 and not 975.0, of course.

Which version of PowerPivot / Excel are you using?

Olaf Helper

[ Blog] [ Xing] [ MVP]

• Proposed as answer by Wednesday, September 18, 2013 9:05 AM
• Marked as answer by Thursday, September 26, 2013 3:03 AM
Tuesday, September 17, 2013 8:16 AM