Sum if a value matches in Pivot Table using an unrelated Dimension
I have a transaction table, using 2 different dimensions say Region_a, Region_b of that transaction I have created a new dimension say Region.
This new Region column is loaded as Inline(there could be a better way) script as follows:
The reason for doing this is that I need to create a Pivot Table which needs to have all the Regions and the Pivot table needs to be like below:
Difference can be calculated once am able to calculate Sales. Is there anyway using Set Analysis I can write that if Region matched Region_a then give me sales or any other better method. I have tried Sum (if), Set analysis stating Region=Region_a but none of them are working. Strange thing sum(if) is not giving me correct data for all the regions. Any help would be great!
@swuehl , @sunny_talwar Any suggestions? I have found a fix by making Region columns as per the specific concatenated tables in the transaction but am not that happy with this fix, looking for a better solution. Please help
@sunny_talwar Apologies for the late reply! Even though all the transaction tables are concatenated, there are some tables having salesamount for Region_a mapping and others having sales of region_b, so I created a common field region as below:
Load*,Region_a as region
Load *,Region_b as region
I did this for all the tables concatenated and then I used the sum as required.
Earlier, I tried creating an island table which concatenated all the regions. After that I was trying to use sum(if) but that didn't work. Luckily the tables were little different and this method worked but, there would definitely be a better way.
I also tried linking that data island but since some tables had Region_a and other had Region_b, it didn't work.