Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
OmarBuenrostro
Frequent Visitor

Single goal column for 2 different hierarchy columns (i.e. State and Region)

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.

StateRegionGoal
California 300
 Los Angeles150
 San Diego100
 San Francisco150
   
Arizona 200
 Phoenix120
 Tucson120
   
Nevada 200
 Las Vegas150
 Henderson100
   
Utah 200
 Salt Lake City150
 Provo100

 

Here is some sample data to work with

 

Thanks in advance!!

2 REPLIES 2
wdx223_Daniel
Super User
Super User

it's better to provide some sample data.

Hi, here is a sample data link

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors