Multiplication of 2 columns based on another column

[akashpadmakumar]

HI,

I have 3 columns named "Reporting Currency Amount" ,"Markup Rate" and "Reporting Period"

Reporting Currency has Decimal values in $, Markup rate is mostly % values (eg. 5%, 10%...) and Reporting Period has only 2 values - Prior Period and Current Period.

I want to create 2 measures -

Used Rate PP and Used Rate CP -

The calculation expected is Used Rate PP = [(amount1*rate1)+....+(amount10*rate10)]/[amount1+...+amount10] for all Reporting period "Prior Period " and same for Used Rate CP.

Sample Data as above, Please help 🙂

@PaulDBrown @johnt75 

[akashpadmakumar]

Thanks man 🙂
Rockstar

[Shishir22 · Accepted solution]

Hello @akashpadmakumar ,

I have given table name as DATA.

Please try -

Used Rate PP = 
VAR _Num = SUMX(FILTER(Data,Data[Reporting Period]="Prior Period"),Data[Reporting Currency Amount]*Data[Markup Rate])
Var _Den= SUMX(FILTER(Data,Data[Reporting Period]="Prior Period"),Data[Reporting Currency Amount])
return
DIVIDE(_Num,_Den,BLANK())

Used Rate CP = 
VAR _Num = SUMX(FILTER(Data,Data[Reporting Period]="Current Period"),Data[Reporting Currency Amount]*Data[Markup Rate])
Var _Den= SUMX(FILTER(Data,Data[Reporting Period]="Current Period"),Data[Reporting Currency Amount])
return
DIVIDE(_Num,_Den,BLANK())

Please mark it as solution if it solves your issue. Kudos are also appreciated.

Cheers,
Shishir Shrivastava