Planned vs Actuals in Metrix

[NithinBN]

Hello All, 

I am trying to come up with a Metrix visula where it shows planned vs Actual sales (Based on Employee and Product)

My planned  tables looks like this: 

My Actuals table looks like this:

I want visuals like this: 

Data is not showing in proper way. 

I also want to highlight green if Actual number is more than planned and Red if Actuals is less than planned. 

Please suggest how I can do it. 

Thanks in Advance.

[Jeanxyz]

@NithinBN I have done something similar in the report below. 

The Recorded Hrs are actual amounts, actual amounts are highlighted based on recorded hrs vs planned hrs %. The highlighte is done via conditional formatting -background color. 

Let me know if you need more details.

[Ashish_Mathur]

Hi,

In each table insert a column called Type and have the following entries - Actual and Budget.  Using the Query Editor, append the two tables.  Create a matrix visual and write this measure

Quantity = sum(Data[Qty])

Hope this helps.

[NithinBN]

Hi, 

Is there any way i can extablish the relation between 2 table ? 

Appending data will duplicate the data right ? 

[Ashish_Mathur]

You may create 2 Dim tables - one each for Name and Product.  Create a relationship (Many to One and Single) from each of the 2 Fact tables to the Dim tables.  To your visual, drag Name and Product from the Dim tables.  Write these measures

Planned = sum(Budget[Qty])

Actual = sum(Actual[Qty])

[Anonymous · Accepted solution]

Hi @NithinBN 

Step 1: Create Calculated Table 1 

DimReps = SUMMARIZE('Actuals Table','Actuals Table'[Name ])

Step 2: Create Calculated Table 2 

DimProduct = SUMMARIZE('Actuals Table','Actuals Table'[Product])

Step 3: Build your Relationship

Step 4 : Plot it on a Matrix

If that helps, please mark it as solution .

Regards

Dax_Noob

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...



https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523