Try to use SUM(Quantity*Price) for the calculated field instead of SUM(Quantity)*Sum(Price).
I get the error "All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources"
1 of 1 people found this helpful
With data blend you can't create table calculations without aggregation. I have tried my hand at finding the solution though there might be something better. The 2 example datasets that I selected were:
Dataset 1 Dataset2
Date Item Quantity Date Item Price 6/16 Chalk 5 6/16 Chalk 9 6/16 Food 4 6/16 Food 12 7/16 Fruits 2 7/16 Fruits 3
As you can't combine table calculations without aggregation, the solution lies in starting at the most granular level of blend i.e Date & Item to calculate the Revenue. Here are the steps:
1) Create a calculated Field Revenue which is sum(Quantity)*sum(Price)
2) Create the table calculation 'Running Total of the Revenue' as shown in the screenshot below.
Select Advanced in the Running Along.
Select Date in the Restarting Every.
The last row of your Date/Item combination contains the answer you are looking for.
3) To keep only the last row create a second calculated field Whether Last or not using the Last() function and selecting the default table calculation (blue font) with the same selections as Running total above i.e, Advanced selecting Date & Item and select Date in the Restarting Every.
4) Put it in the filter shelf and select At Most value as 0.
5) You can hide the other fields except date by deselecting the show header as shown below. It also shows the solution: