Pull Goals into a Matrix, then do calculations

[ChiliGuy]

I am attempting to reproduce a pivot table I created in Excel, which displays YTD sum totals by a value named "Finance Categories". I then added a few columns next to the pivot table to display the goals for the year and calculate our "Balance to Goal" and "% to Goal". I then added a column that divides the goal by 12 and multiplies the result by the number of the month in the fiscal year, and calculates the "YTD % to Goal" (to gauge whether or not we are on track with where we should be).

I'm connecting to my data source (Oracle) and using Direct Query to pull the same data into Power BI, which I call "Production". Pulling that data into a Matrix with "Finance Categories" as the rows and the Transaction Amounts into the Values works great. But when I create a second dataset querying the table that holds the goals, I can't seem to pull that data into the Matrix to display the goals for each Finance Category.

I have created the "relationship" between the two datasets by linking the "Finance Category" in the Production dataset with the "Finance Category" in the Goals dataset, but that doesn't seem to help... PBI wants to do some sort of calculation on the goals, and "do no calculation" doesn't seem to be an option.

I think if I could figure out how to do that, I could figure out how to create the measures to do the "Balance to Goal" and "% to Goal" calculations, but I'm stuck at this step. Any ideas or suggestions would be appreciated.

Steve

[andrebernardes]

I have created the "relationship" between the two datasets by linking the "Sales" in the Production dataset with the "Target Sales" in the Goals dataset, but that doesn't seem to help... PBI wants to do some sort of calculation on the goals, and "do no calculation" doesn't seem to be an option.

Resolution of the first part of the problem, which is to display the Target value in the Matrix.

How?

Still in the Power BI 'Table Display' area, after using a DAX function to retrieve the Goal value from another table, adding it as a new column in the current table, convert it to text:

Target = CONVERT( LOOKUPVALUE( Sales[Target], TargetSales[Cod], Territory_Sales[Cod]), STRING)

This way, the retrieved Target will not be aggregated or undergo any calculation action when shown in the Matrix in the 'Report Display' area of Power BI, as it will appear as the First Value.

Resolution of the second part of the problem: Perform calculations based on the retrieved Target. If you need to use the value for specific calculations, simply use it without the corresponding conversion to text in the 'Table Display' area of Power BI.

[Anonymous]

I'm just playing around with my own data set and I don't have that option in matrix either, but do have the option in a table. Does it need to be in a matrix?

[v-yuta-msft]

Hi jd009,

You can create a YTD% measure or calculate column and then pull that measure or column into your matrix visual.

Regards,

Jimmy Tao

[v-yuta-msft]

Hi ChiliGuy,

"But when I create a second dataset querying the table that holds the goals, I can't seem to pull that data into the Matrix to display the goals for each Finance Category."

<--- Which columns have you put in your matrix? Could you share some details about your calculate column "YTD % to Goal" and your expected result?

Regards,

Jimmy Tao