Goals without average

[KrisB2019]

Hi! I'm pretty new to Power BI and have only scratched the surface of its capabilities. This is probably very simple for most of you, but it's making me crazy and I'm hoping your expertise can help me. The issue I'm running into right now is how to show goals and actuals against the goals. For example, each salesperson has a goal by month for 2019 and from my sales report, I want to show how many sales were achieved compared to goal and I want to show it so that the user can then view the records of the actuals. I'm pulling the goal data from a tab in my spreadsheet that I connected to through Power BI and below is the sample. I created a relationship between salesperson from the table below and the table containing all sales. I was hoping that relating the two tables would solve the problem, but I believe I'm missing another date calculation somewhere. I also created a new bin for sales date by month. In BI, I can bring in all sales and invidual monthly goals, but I can't show a 1-1 relationship of goals/actuals by month, if that makes sense. Everything I googled so far seems to show something a bit different than what I need or suggests creating a power pivot and KPI in Excel to then import in BI. Any suggestions or directions to additional articles you might know of would be greatly appreciated! Thank you!

SalesPerson	January Goal	February Goal	March Goal
A	10
B	16	18	20
C	10	12	14
D	17	19	23
E			3
F	7	9	12
G	12	14	17
H			0
I	17	19	23
J	20	22	24
K	20	22	24
L	17	4	8

[Anonymous]

@KrisB2019 ,

Here's what I came up with. You have a Sales and Goal table , which will be your Fact Tables.  Created a DimSalesPerson which is just a one-column ( could be more ) table of the unique sales people.  Also need a dedicated Calendar table. I added that with a small function.  All which can bee seen in Power Query.  Also, there's another table called PivotedGoal, where I show how to transform the table you get from excel into one that works so much better with DAX and PBI.  Also you can see in the applied steps in the attached file below.  But here's the data model:

So we will use Dates from the new DimCalendar table and Salesperson from the new DimSalesPerson table for our filters ( row, columns, slicers, ect.  

We start with two base measurs. Total of Sales and Total of Goals:

Total Sales = SUM ( FactSales[Amount] )

Total Goal = SUM( FactGoal[Goal] )

That gives us the total of sales for each day and then the Monthly goal on the 1st of the month.  The FactGoal table needed a date (not just a month name) so I chose the 1st, which makes the most sense if these are monthly figures.  

Then we are interested in what the running month-to-date figure is for sales ( so we can see where we are in conjunction with the goal):

Total Sales MTD = 
IF( 
    NOT( 
        ISBLANK( [Total Sales])
),
TOTALMTD( [Total Sales], DimCalendar[Date])
)

all that says if I have a total sales figure, give me the total from that day all the way back to beginning of the month. If there are no sales, I dont want anything.

But since we have daily sales, we need that monthly goal figure available every day so we can compare.  This will give the current month's goals in each day we have sales ( you wouldnt actually put this on a table since it will just be repeating, but we need it in future calculations.

Total Monthly Goal every day = 
IF( 
    NOT( 
        ISBLANK( [Total Sales]))
    ,
CALCULATE( [Total Goal], ALL( DimCalendar) )
)

 Then the last measure is the take the total of sales month-to-date and divide that out by the monthly goal:

% of Goal = 
IF(
    NOT( 
        ISBLANK( [Total Sales])
        ),    
    DIVIDE( [Total Sales MTD], [Total Monthly Goal every day])
)

and here's the final table:

It can be a complex subject and I just scratched the surface here, but I hope it gets you started in the correct direction.

Here's the PBIX file:

https://1drv.ms/u/s!Amqd8ArUSwDS0RrdxZ_ztcJ5MyV-

[KrisB2019]

It makes me feel so much better that you said it could be a complex subject! I really thought I was missing one simple date measure. I'll get started with what you have suggested and let you know how it goes. Thank you so much for your help! I do have a calendar table so I'm off to a good start. I have a lot to learn, thanks again!

[Anonymous]

It can be a little complex, but much easier to break it down into smaller sections and go from there. Just have to put in some time and you'll get it 

[KrisB2019]

@Anonymous , I've replicated all your suggestions except FactSALES. I'm trying to pull in the number of sales from a related table so that it gives me a count of sales by person like a pivot table would in Excel. Then I can duplicate your matrix. However, it is pulling in everyone on the list and I can't determine the best filter to use in order to limit the number of records pulled. I think it should be a date one, but am not certain. See below snippet. Any suggestions? Thanks again for all your help!