Dynamics Comparisons - Stores vs Competition

IH8DATA

New Member
Joined
Oct 16, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I am using CPG data and need help comparing a companies sales data to their competition. In the sample data you will see 4 unique entries for "Company", Acme, Acme Competition, Smart Div and Smart Div Competition. In this example I want to compare the sales for Acme against the sales for Acme Competition. Ideally I would have my user select a slicer for Acme (not Acme Competition) and display both the data for Acme & Acme Competition.

Acme Div will ALWAYS be compared to Acme Competition such as Smart Div will ALWAYS be compared to Smart Competition. Additionally both Acme and Smart are under the same umbrella so our user may select both Acme and Smart. That means that the aggregation of Acme Div and Smart Div would need to be compared to the aggregation of Acme Competition and Smart Competition. My initial thought was to create a lookup table and have Acme as a parent company so that when I select Acme I can see both companies.

I am trying to stay away from hard coding companies, such as a measure like, Acme Competition Sales:= CALCULATE( [Sales] , data[Company] = "Acme Competition" ). I am also trying to stay away from cubes.

Hope this was *somewhat* clear.

Thanks
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top