Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi community,
I am trying to figure out a way to insert 1 new column that will show the sales goals for each State and region, but it depends on 2 separate columns (State and region) so for example if the matrix is colapsed and only shows states, the column will show the corresponding goal for that state, once the row is expanded to show the cities or regions, the column will also show the goal for those cities/regions and mantaining the State goal visible as if it were a subtotal.
Since we are working here with 2 columns and not 1, it seems more dificult for me to come up with a solution. Maybe there is a function im not aware of or a trick or something simple that i havent learned yet.
I tried to investigate and had no success, but i think this seems something common that any sales team would want to have, so i guess it has already been done before.
The data source for the goals would come from 1 or 2 new tables since there are many more regions than the example below, which makes a manually conditional column not a very viable solution.
This is an example of how the matrix would look like, the goals will not be calculated, but provided from upper managemet.
State | Region | Goal |
California | 300 | |
Los Angeles | 150 | |
San Diego | 100 | |
San Francisco | 150 | |
Arizona | 200 | |
Phoenix | 120 | |
Tucson | 120 | |
Nevada | 200 | |
Las Vegas | 150 | |
Henderson | 100 | |
Utah | 200 | |
Salt Lake City | 150 | |
Provo | 100 |
Here is some sample data to work with
Thanks in advance!!
it's better to provide some sample data.
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |