I have a small sample table, which I have added to the Data Model. I want to add a NEW COLUMN named [Total_value], which is calculated by multiplying 2 columns named [A1] and [B1].
Then I want to do AGGREGATION based on this newly calculated column of [Total_value], in such a manner, that instead of getting the values for each STORES separately, I get just one value for each PRODUCT per state of Texas and Florida.
The Final Result will look something like this, showing only the Aggregate Values, for Each State, for Coca Cola and Pepsi, on each dates.
I am a total newbie in Power Pivot and I have no idea about what method would be best to get the output that I need. I have added a very small sample table, the real table is more then 1000 times bigger then this, therefor it is a must that we make use of the Power Pivot Data Model etc. for doing these calculations, otherwise doing such calculations on the real table by using normal excel column calculations etc. would hang the whole sheet.
Please suggest some ways by which we can get the desired output. And please do not suggest doing it in excel directly, without using Power Pivot Data Model, because that is not practical in my case. I need to take the help of the Data Crunching Power that is available in PPDM, because the data size is HUGE.
If you have any doubts, then please ask them, and I would explain in more details.
I am using the following software versions -
Microsoft SQL Server Management Studio version- 12.0.2000.8,
Microsoft Office 2013 x64
and Windows 7 x64
I have attached the Excel File having the data.