Multiply measure by column and sum total

[sr001]

Hello All,

I have read the other discussions on this topic and am still having issues with my report. 

I am trying to create a measure (or column) which will multiply Hours by Average ESR (15 * 96.14) for each row and then sum the total cost. Have tried to follow other suggestions but am ready to give up. Any help appreciated.

Thank you

[Zubair_Muhammad · Accepted solution]

HI @sr001

I am not sure... but please give a SHOT to this MEASURE

MEASURE=
IF (
    HASONEVALUE ( '001'[Grade] ),
    SUM ( '001'[Hours] ) * AVERAGE ( 'ESR Rates'[ColumnName] ),
    SUMX (
        ALLSELECTED ( '001'[Grade] ),
        CALCULATE ( SUM ( '001'[Hours] ) )
            * CALCULATE ( AVERAGE ( 'ESR Rates'[ColumnName] ) )
    )
)

[sr001]

Thank you @Zubair_Muhammad !! 

That has worked perfectly, your help is much appreciated. 

I see (and think I understand the need for) that you have used SUM and SUMX, but what is the requirement for the HASONEVALUE line please.

Again, many thanks for your help.

[Zubair_Muhammad]

Hi @sr001

Sorry for late reply... i was out of work

Actually ....it happens frequently with MEASURES that the sum of individual rows do not match the TOTAL ROW.

Check this precious post of @Greg_Deckler..

http://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/m-p/63376#U63376

HASONEVALUE lets you specify different formula for individual rows and the Total row