Forum Discussion

hayali01's avatar
hayali01
Regular Visitor
5 years ago

Filter by Value code in Measure

Hello, I want to select certain cost codes in the cost code column to allow divison by another value. In a very simplified version I want to total the cost of the A codes and divide by the average area of A's. I need the formula to write this. 

 

Cost CodeCOSTArea (m2)
A.1£110
A.2£210
A.3£310
A.4£410
B.1£520
B.2£620
B.3£720

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    5 years ago

    hayali01 

    =VAR _c=LEFT(MAX('Table'[Cost Code]),FIND(".",MAX('Table'[Cost Code]),,99)-1) RETURN  CALCULATE(DIVIDE(SUM('Table'[Cost]),AVERAGE('Table'[Area (m2)]),0),LEFT('Table'[Cost Code],FIND(".",'Table'[Cost Code],,99)-1)=_c)
  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    hayali01 , Create a measure like 

    calculate(divide(sum(table[COST]), averageX(Table,table[Area (m2)])), filter( Table, left(Table[Cost Code],1) ="A"))
  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Hi, hayali01 , why not make a small transformation to the dataset, which saves a lot of trouble and brings about much clarity in authoring measures.

    Avg Cost = 
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[COST] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Region] = MAX ( 'Table'[Region] ) )
    ),
    CALCULATE (
        AVERAGE ( 'Table'[Area (m2)] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Region] = MAX ( 'Table'[Region] ) )
    )
)