locked
how to create a measure based on another two measures in DAX? RRS feed

  • 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

Answers

  • 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 Elvis Long Wednesday, September 18, 2013 9:05 AM
    • Marked as answer by Elvis Long Thursday, September 26, 2013 3:03 AM
    Tuesday, September 17, 2013 8:16 AM