I am new here and hope that I selected the correct category for my question.
So what I need to accomplish is the following:
I have locations and the information on their actual registrations and what their registrations were projected to be. This information is broken down by month.
I need to be able in a table or chart reflect actual against projected registrations. I also need to be able to show the variance between actual and projected. While the attached file is presented in one spreadsheet with a tab for projected registrations and a tab for actual registrations they are technically separate files. The other conundrum I had which I solved by creating a common table is that the name of a location in the Projected data will be slightly different than the name of the location in the actual registration data. So in a common “in memory” table I created a code which is the following
Ok, so there’s a number of steps in combination here which are the key to getting this right.
Most of the hard work is setting up the model right, and then the formula will be easy from there.
I’ll give you a few ideas around how I would do it, but ultimately there’s probably a few way you can do this and it will be your preference as to what you do in the end.
The first major thing that has to be done with data like this is to ‘unpivot’ it. You want your dates/months in one column and the results in one column also.
You also need to somehow get a real date into the table, so you can use the time intelligence functions in DAX and also utilize a date table as a lookup table.
To do that, I used the ‘columns from examples’ and starting typing in the first date of the month, and included the actual month from the column available. Then changed the type to date.
Try some of these tips and get the model right first that is the key.
Around having difference names. I think the best solution there is to choose what name you want to use across both tables and then use the ‘replace values’ transformation in the query editor to normalize them
Does this have to be done one by one using replace value? There are 120 locations so do I need to do the replace value 120 times so that across both tables the names are the same?
Somehow you’ll have to create a link between the two table with the same names.
By replacing them to a ‘standard’ name if you like, is one way.
Another is to have a supporting table, like an index table that has every single name variation possibility and then next to it the standard name. Using this table you can then create a calculated column in the actual and projection tables which refers to the name index reference, bringing in the correct name
You’ll probably want a lookup table here with all the location names actually. This way you’ll be able to filter across both tables by the locations etc.
This is what my tables look like as of now. I actually did create a “Location Code” in memory table that has all the possible names for a location i.e. Albuquerque, Albuquerque Center, Albuquerque Campus. They are linked through the Location ID Number.
I will work on the suggestion provided for the actual and projected locations. I didn’t even think of it from that angle. I just went straight to trying to display it and work my way backwards.
Certainly jump into this course as soon as you can. There’s a lot I would adjust to that model. All my best practice tips are covered in detail in this course
The model is crucial to get right before you move ahead.
