How to Multiply multiple value that will output one/same column using measure

[kristeljoymalapitan]

Hi , can someone help me how to multiply using different value but results on the same column?

I have the dpd column the default percentage column and the OB amount column below

• Dpd 0  - 0.01 - 500
• Dpd 30 - 0.02 - 600
• Dpd 60 - 0.03 - 700

The computaions will be:

Dpd 0 - 500*0.01 = 5

Dpd 30 - 600* 0.02 = 1.2

Dpd 60 - 700*0.03 = 21

The results will be on one column using measures

5

1.2

21

Thanks

[Tim_Wensink]

Hi,

I am not exactly sure what you are trying to achieve, but if you have the columns you mention in one table, perhaps it is an option to add a measure to your table with the expression = [percentage column] * [OB amount column]? That measure would then show the result for the calculation for each row . You should of course use the exact field names as available in your app. 

Greetings,

Tim Wensink

[JordyWegman]

Hi Kristel,

Is this data in the same column?

• Dpd 0  - 0.01 - 500
• Dpd 30 - 0.02 - 600
• Dpd 60 - 0.03 - 700

Or it more like this with DPD as header and the data is in the column?

• DPD
• 0  - 0.01 - 500
• 30 - 0.02 - 600
• 60 - 0.03 - 700

If it is the second one you can try this:

Table:
Load
  Key,
  Amount * Multiplier as Result
;
Load
  Subfield(DPD,' - ',1) as Key,
  Subfield(DPD,' - ',2) as Multiplier,
  Subfield(DPD,' - ',3) as Amount,
From [YourSource](qvd);

Jordy

Climber

[kristeljoymalapitan]

Hi Sir Jordy,

Below data are in the different column

dpd 0,30 and 60 is under BUCKET column

0.01,0.02 and 0.03 is under MULTIPLIER column

500,600,700 is under AMOUNT column

• Dpd 0  - 0.01 - 500
• Dpd 30 - 0.02 - 600
• Dpd 60 - 0.03 - 700

the the results will be on the new column.

Can we make this in  measure expression?

[JordyWegman]

Hi Kristel,

Yes this is possible. Are you doing this in the script or in the Data Manager?

Script:

Table:
Load
  Bucket,
  Amount * Multiplier as Result
From [YourSource](qvd);

Data Manager:

Create a new measure where you enter: Amount * Multiplier

You can then give it the name Result (or any other name that you prefer)

Jordy

Climber

[kristeljoymalapitan]

Please see sample screenshot  for your reference

I used below measures to multiply amount of dpd 0 to 0.01

SUM({<[CURR.BUCKET]={'0'}>}[AMOUNT])*0.01

Thanks

[kristeljoymalapitan]

Sorry for confusion, my multiplier is not a column,
it was manually multiply in measure expression, please see sample screenshot for your reference.

What I am thinking is how to multiply in multiple way using one measures

[kristeljoymalapitan]

Hi Tim,

I used measures expression,  percentage * OB amount, however percentage is not a column, I need to manually multiply, How can I multiply OB amount with different percentage per BUCKET

BUCKET               OB_AMOUNT          MULTIPLIER            RESULTS

dpd 0                           500                              0.01                            5

dpd 30                         600                             0.02                            12

dpd 60                         700                             0.03                            21

I used sample measure in my expression

SUM({<[BUCKET]={'0'}>}[OB_AMOUNT ])*0.01  -- this expression will output as 5

My question is how can I apply or get output for dpd 30 and 60 on the same column,

How can I multiply multiple values on each bucket that will result in one column, please see below sample screenshot

[Tim_Wensink · Accepted solution]

Hello Kristel,

If you have a fixed percentage per bucket, I would create an Excel file containing:

BUCKET     MULTIPLIER

0        0.01

30     0.02    

60     0.03

You can then load this excel in your load script, so that every bucket has its own multiplier which you can then use in the table you show by using OB_AMOUNT * MULTIPLIER as formula. 

If you want to use this in an expression the formula would be:

SUM({<[BUCKET]={'0'}>}[OB_AMOUNT ])*only({$<[BUCKET] = {'0'}>}MULTIPLIER)

would this work for you?

kind regards,

Tim Wensink

[kristeljoymalapitan]

Hi Sir Tim,

I will try  your suggestion, and update if this will work

Thank  you