Sales values and their targets

[Anonymous]

Hello,

I have two tables: a sales table ( called 'Results' in the attached) and a table with targets ('Targets').

I would like to create a table, where I would like to see the sums for every manager. And also a column with targets for every manager, where the total is the sum of all the targets (i.e. 100)

So far I can add just the targets for every manager, but I do not know how to add the target total.

Please find the file attached:

https://file.io/ploTUJXuWwfb

[123abc]

Here are the steps you can follow:

1. Load Data:

   • Load both 'Results' and 'Targets' tables into Power BI.

2. Create Relationships:

   • Ensure that there is a relationship between the 'Manager' column in the 'Results' table and the 'Manager' column in the 'Targets' table.

3. Create Measures:

   • In the 'Results' table, create a measure to calculate the sum of sales for each manager. Let's call this measure 'Total Sales':

Total Sales = SUM('Results'[Sales])

In the 'Targets' table, create a measure to calculate the sum of targets for each manager. Let's call this measure 'Total Targets':

Total Targets = SUM('Targets'[Target])

Create another measure to calculate the overall target total. Let's call this measure 'Overall Target Total':

Overall Target Total = SUM('Targets'[Target])

1. Create a Table:

   • Create a new table or use an existing one.
   • Place the 'Manager' field from the 'Results' table in the Rows or Columns area.
   • Add the 'Total Sales' measure and 'Total Targets' measure to the Values area.

2. Display Overall Target Total:

   • To display the overall target total for all managers, add the 'Overall Target Total' measure to the Values area of the table.

Your table should now show the total sales and targets for each manager, as well as the overall target total.

Remember that the exact steps might vary slightly depending on your specific data model and requirements. If you encounter any issues or need further clarification, feel free to provide more details, and I'll do my best to assist you.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Anonymous]

Hi @123abc ,

The particularity of the 'Results' table is that there is no 'Manager' field. 

Even if there is one, the target total propagates to the 'Results' table as the total for all of the values.

[123abc]

If there is no 'Manager' field in the 'Results' table, and the target total needs to propagate to the 'Results' table, you can follow these modified steps:

1. Create Relationships: Ensure that there is a relationship between the 'Manager' column in the 'Targets' table and the 'Manager' column in the 'Results' table. If there is no direct 'Manager' column in the 'Results' table, you might need to use a related column or another common identifier.

2. Create a New Table: Go to the "Model" view and create a new table by going to "Modeling" > "New Table."

3. Write DAX Measures: In the new table, use DAX measures to calculate the sums for every manager and the target total.

TotalSales = SUM('Results'[Sales])
TotalTargets = SUM('Targets'[Target])

1. Display the Data in a Table: Now, you can create a table in the report view and add the 'Manager' column from the 'Results' table (or any related table) and the measures you created in step 3.

   Manager Total Sales Total Targets

   Manager1	[TotalSales for Manager1]	[TotalTargets]
   Manager2	[TotalSales for Manager2]	[TotalTargets]
   ...	...	[TotalTargets]

   Replace [TotalSales for Manager1] and [TotalSales for Manager2] with the actual DAX expressions for calculating the total sales for each manager. Ensure that you use the appropriate related column or identifier to display the manager in the 'Results' table.

2. Propagate Target Total to Results Table: To propagate the total target to the 'Results' table, you can use a DAX measure that utilizes the RELATED function to fetch the total target.

TargetTotalForResults = RELATED('NewTable'[TotalTargets])

1. Add this measure to the 'Results' table to display the total target for each row in the 'Results' table.

Adjust the DAX expressions based on your actual column names and data model. If you encounter any specific issues or need further assistance, feel free to provide more details.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.