create two different measure column sum(Sold),avg(Commission) at product level then try this SUM(sum(Sold) * avg(Commission)).
Hi, I tried that, but I still get one row per product. I don't know how to make it so it only shows me one row, since I don't want to see the Products, I don't want to have the product column in the report at all.
You can try this:
1 - Use your two measures without any advanced config (sum by..., use only sum aggregate on RPD);
2 - In the report, drag-and-drop any column and then edit it formula as bellow:
sum("Sold" by "Product") * avg("Commission" by "Product")
where "Sold" is your sold measure
"Commission" is your commission measure
"Product" is your attribute column
Ensure your column aggregate is set to SUM (into column formula) and DON´T use your column Product in this report
Mark right if helpful
Hi, thanks for the replies, but I still can't get it to work.
I tried that, but I am still getting the same result as doing the simple (Sold * Commission).
The problem is that they need first to compute each value by Product Name, and then sum all the (Sold * Commission) by each product. That is the total they need. In one row, without the Product names in the report.
Perhaps you need to store the extended commission at the product level in the warehouse first... start by using a view (mat. View or view either will do the trick) that contains your unique keys for product and sale and the fact of sum(sales) * avg(commission). Expose that in your physical layer, build a physical and logical star around it ... now you only have to worry about the aggregation of SUM on the product of that new fact column.
sum(Sold by product) * avg(Commission by product)
in this way try for other options to calculate on fly.