Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Hi Kristel,
Is this data in the same column?
Or it more like this with DPD as header and the data is in the column?
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
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
the the results will be on the new column.
Can we make this in measure expression?
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
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
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
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
Hi Sir Tim,
I will try your suggestion, and update if this will work
Thank you