Calculated Column returning correct totals per row in model, but summarising in visual

[kristi_in_heels]

Hello all,

I am trying to create a simple bar chart to show the $ variance between a planned and a forecast value, per project.

I have 2 separate tables showing the required columns, and have created a measure to complete the calculation.

The calculation is returning the correct result against the data showing in the tooltip fields, but one of the fields being used in the calculation is returning the column total, as opposed to the project row total, and therefore the calculated result is incorrect.

The project row total shows correctly in the model, and there is a relationship between both tables project number so the filters are working as intended.

Is there a way to ensure the value pulling through to the visual is per row and not total column? 

The $255M value shown below is the total of all projects, not the individual project, but in the model there is an inidividual result per project.

I want to see the 'F0x Total' Value used in the calculation to return a project specific result. I have tried removing summarisation, changing to min / max / count / average / whole number / text - almost every formatting option I can access, to no avail.

Have I used the wrong calculation in my column calc to get the initial result and this is throwing everything out?

F0x Total = F0x[July] + F0x[August]+ F0x[September] + F0x[October] + F0x[November] + F0x[December] + F0x[January] + F0x[February] + F0x[March] + F0x[April] + F0x[May] + F0x[June]

Thank you

[JoeBarry · Accepted solution]

Hi @kristi_in_heels 

From looking at the screenshot, it seems that the dataset is setup incorrectly for Power BI. From what I can see you are importing the data as a Pivot table. Whereas, Power BI needs the data in Tabular format, meaning there is a row for each month instead of columns like in your data.

If the Pivot Table in the screenshot is a generated Pivot table from another tab in Excel, then import this Tab instead of the Pivot table.

If the data is just being collected this way, you can convert it to a Tabular format in Power Query. Remove the total F0x column,  Highlight all month columns and right click on one of the highlighted columns and choose Unpivot Columns. Rename the column containing the months to Months

From what I can see, you only have a Month Name. You will need a date column to compare.  Highlight the month column, click on Add Column and add this Code 

if [Month] = "January" then 1 else
if [Month] = "February" then 2 else
if [Month] = "March" then 3 else
if [Month] = "April" then 4 else
if [Month] = "May" then 5 else
if [Month] = "June" then 6 else
if [Month] = "July" then 7 else
if [Month] = "August" then 8 else
if [Month] = "September" then 9 else
if [Month] = "October" then 10 else
if [Month] = "November" then 11 else 12

Convert this column to text.

To create a date column, add a new Calculated column

for a dd/mm/yyyy column add the code like this 

"01/" &[Month] & "/2023"

for a mm/dd/yyyy column

[Month] & "/01/2023"

Repeat for the 2nd table.

Now to compare the two tables, they will need a table that will create a relationship between them. These are called Dimension tables. You want to create a Date Dimension table and a Dim table with a column that both tables share, this usally an ID column. Maybe in your case its a ProjectID?

To create this, duplicate both tables. Remove all columns in both tables except for the ID column. Highlight one of the tables and in the ribbion, click on append queries and choose the 2nd table. Please note, the column name must have the same name and format. Otherwise it will create two columns. After this highlight the column and choose remove duplicates. You now have a unique column. 

Load the data into the report. In the relationship view, create a One to Many Relaionship between the Created table on ID to both ID columns in the other tables. The Same with the Date table, create a relationship between the Date column and the date columns in the other tables.

To make comparison between both tables. You need to create measures.

Create an Actuals measure 

ProjectActuals = SUM(Projects[Amount])

Create an Plan measure 

ProjectPlan = SUM(ProjectPlan[PlanAmount])

You can start making various comparisons

Diff Actuals/Plan = [Actuals] - [Plan]

Actuals/Plan % = DIVIDE[Actuals], [Plan])

Convert to %

Hope this helps

Joe 

If this post helps, then please Accept it as the solution

[kristi_in_heels]

Just a quick update - I ended up reformatting the base excel data to bring in a single month column, rather than individual as in the original. Although I had not pivoted the data, I believe our system exports in somewhat of a pivot format.

I modified the month column calculation to also provide a year option, as this data spans over 2023 & 2023, and added the date column as suggested, bringing the month & year in together. I already had a date table, so created the relationship and adjusted my already created measures to read from the new data table and the chart now works perfectly.

Thank you again and I have marked your response as the solutin.

[kristi_in_heels]

Thank you so much for your comprehensive response.

I will try all of the above as outlined and come back to you with the outcome.

Just one note: the screenshot I posted is the data in the BI model, which was loaded from a basic excel sheet (not a pivot). Does this change any of your suggested solution? 

Thansk again, I really appreciate you taking the time to step this out so clearly.