Hi. I have a can't find a way to generate a calculation at a row level and then aggregate.
I have a transaction detail level source with the Transaction ID, the Product Code, the Quantity sold and the Discount as a percentage (0.03 in data = 3% discount). There is no sales amount.
The price of the product is in another catalog.
To get the Transaction Sales Amount I have to make a calculation where SalesAmount = (Quantity*Price)-(Quantity*Price*Discount). When I do all this calculations at Transaction level everything works fine.
But when I use the sames calculations at Product level to know the Product with more sales the calculations are done at a product level making a SUM for the Discount and then making the calculation, thus the Discount sum of all transactions is greater than 100% (in this example 2.7=270% discount) so the Total Sale comes to be negative.
How can I make this kind of calculations in Lumira Discovery to set the level at which the calculation should be done? In this case the calculation should be made first and then SUM the TotalSale calculation.
Hello Santiago - what is your data source? Is it a BEx query?
Tammy Powlas the two sources are CSV files that are located in 2 different remote folders managed by different areas.
Hello,
What calculation on discount you would like to do for Discount measure. As i understand, the problem is that you have Sum as an aggregation at the Discount level. It's better you choose Average there. This way you get to see the average discount at product level.
Does that help?
Regards,
Ashutosh
Thanks for your answer Ashutosh Rastogi. As you say I have a SUM aggregation for the DiscoutPercentage. If I choose Average instead it helps reduce the error presented, but I don't get the correct numbers. This is quiete a simple and common treatment of data, to lookup for reference prices or interest rates for a product, then make the calculation, and then get the sumarized results. But it seems not to be simple at all in Lumira.
To explain better with a small data data set I'm attaching an image with the problem.
All I have as hard facts in data are the green columns: Quantity, ProductPrice and DiscountPercentage). I have to present Subtotal, Discount, and TotalSales (yellow columns) information in the visualizations, as that are the interesting figures to analyse, so these yellow columns have to be calculations:
Subtotal = Quantity * ProductPrice
Discount = Subtotal * DiscountPercentage
TotalSale = Subtotal - Discount
In this dataset with 10 rows the desired results in the visualization for all the discounts, and all the sales should be $29,656.31 and $1,069,980.50 respectively.
If I use a SUM for Quantity I do get the 94 products sold. If I use an Average for ProductPrice I would get $8,875.69. Now the first calculation is Subtotal = Quantity * ProductPrice. Using the average ProductPrice I get a Subtotal of $834,314.86 which by now is already incorrect as the real Subtotal for all sales is $1,099,636.80. Furthermore, if I go on and use Averge for DiscountPercentage I get a 4.50%, and making the calculation of Discount = Subtotal * DiscountPercentage I get a very wrong $37,544.17 for a total discount amount, and calculating TotalSales = Subtotal - Discount I get also a wrong $796,770.69 for TotalSales instead of the correct $1,069,980.50.
So you can se my problem here. Is there any way in Lumira to make the calculations in any way, or with any parameters so that I can get the correct calculations? Or must I pre-process all my data with a DataIntegration tool to get all the information precalculated, so that Lumira just have to do sums to provided columns, and not calculate anything?
Hello,
First of all, thanks a lot for detailing out what you have been trying to achieve. By default the calculation is Result which is formula in this case and it's showing you multiple of Sum(Quantity)*Sum(Price).
However, in case you want to rather see Sum aggregation in totals, then right click on the dimension header level (the dimension at which this total should be calculated) and then select totals > select multiple. Now, uncheck results and check the Sum aggregation and you would get the desired value.
Regards,
Ashutosh