Create a new column "Total Variance"

Question

Hello,&nbsp;I have a table that looks this&nbsp;&nbsp;Table Name: Project Costs&nbsp;&nbsp;Columns: ID, ProjectNum, Budget, Variance, POC, Region, Country, SnapshotDate.&nbsp;&nbsp;My problem is I want to calculate the total variance for a particular project. for example;&nbsp;&nbsp;project no.&nbsp; &nbsp; Cost Area:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Budget&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Variance&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Country&nbsp;&nbsp; P04950&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Controls Resale&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 221391.15&nbsp; &nbsp; &nbsp; &nbsp; -47618.01&nbsp; &nbsp; &nbsp; &nbsp; Australia&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Mechanical Resale&nbsp; &nbsp; &nbsp;6805080.41&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;430498&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Software Resale&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;69206.48&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 24861.79&nbsp;&nbsp; &nbsp;total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 24861.79&nbsp;&nbsp; P06271&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Controls Resale&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1036257&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -89855&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;USA&nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Mechanical Resale&nbsp; &nbsp; &nbsp; &nbsp;2903945&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -103948&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Software Resale&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;198477&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9660&nbsp;&nbsp; &nbsp;total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 9660&nbsp;&nbsp; &nbsp;XXXXX&nbsp;&nbsp;1. When I tried to total the variance using the subtotal function in PowerBI, I am getting the wrong total&nbsp;&nbsp;Total variance = (-47618.01+430498+24861.79)= 407742, but I am getting 24861.79 which is wrong&nbsp;2. I need to add new column to get the total variance for particular country, So my new report will be like below&nbsp;&nbsp;&nbsp;Country&nbsp; &nbsp; &nbsp; &nbsp;Total Variance&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;YTD&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;MTD&nbsp; &nbsp;&nbsp;&nbsp;Australia&nbsp; &nbsp; &nbsp; 1241215&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1254&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1254USA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;412542&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;458444&nbsp; &nbsp; &nbsp; 458444XXXXXX&nbsp;3. Also how to calculate YTD and MTD columns if we have POC and Total variance columns&nbsp;Please help me, thanks in advance.&nbsp;&nbsp;&nbsp;Bhavesh

v-xjiin-msft · Answer

Hi&nbsp;Anonymous,&nbsp;1. To calculate the Total&nbsp;variance, you can try to following method:With Calculated Column:&nbsp;&nbsp;Col Total Variance =
CALCULATE (
    SUM ( 'Project Costs'[Variance] ),
    FILTER (
        'Project Costs',
        'Project Costs'[ProjectNum] = EARLIER ( 'Project Costs'[ProjectNum] )
    )
)&nbsp;With Measure:&nbsp;Mea Total Variance =
CALCULATE (
    SUM ( 'Project Costs'[Variance] ),
    FILTER (
        ALL ( 'Project Costs' ),
        'Project Costs'[ProjectNum] = MAX ( 'Project Costs'[ProjectNum] )
    )
)&nbsp;&nbsp;2. To calculate the Total Variance for particular country. Just change above filter from&nbsp;'Project Costs'[ProjectNum] to&nbsp;'Project Costs'[Country].&nbsp;3. To calculate YTD and MTD. You should know that there exists corresponding DAX functions which can be used to calculate YTD or MTD. You can try these functions.&nbsp;&nbsp;By the way, your source table has more columns than your sample data like POC. So please share us more detailed&nbsp;sample data which can help us get your desired result.&nbsp;Thanks,Xi Jin.

anonymous · Answer

Hi Xi Jin,&nbsp;&nbsp;Thanks for your reply. I calculated total variance using the column&nbsp;Total Variance = CALCULATE(SUM('Project Costs'[Variance_Num]),FILTER('Project Costs', 'Project Costs'[Project Number] = EARLIER(('Project Costs'[Project Number]))))&nbsp;But the values are different, I did for a separate region too. But the values are not matching with my sample data. Here what I got in the report.&nbsp;&nbsp;My sample data is shown below.&nbsp;

anonymous · Answer

Also, from the sample data, I figure out that the total variance is the current monthly variance minus the baseline (December) monthly variance per project.

For January the total variance = Total variance of all projects (region) - Variance of all the projects in December (same region)

How do I put that in the power bi report?

I came to know that YTD = Total variance * POC

and MTD = Current month YTD - Last month YTD

Please help me how should I put in the report? Thanks in advance

anonymous · Answer

Hi&nbsp;v-xjiin-msft,&nbsp;Thanks for your response.&nbsp;I tried to calculate Total Variance and YTD valuesTotal Variance = (SUM('Project Costs'[Variance])-CALCULATE(SUM('Project Costs'[Variance]),&nbsp;PREVIOUSMONTH('Project Costs'[Snapshot Date].[Date])))*-1&nbsp;YTD (January) = Total variance (January)* POC(January)&nbsp;But I am unable to find out MTD values for the table.&nbsp;MTD (January) = YTD (January) - YTD (December), this is the calculation.&nbsp;But how can measure MTD values in Power BI report? Please help me, thanks&nbsp;&nbsp;Bhavesh

anonymous · Answer

Hello,&nbsp;I have a table 'Project Costs' shown below,&nbsp;&nbsp;I have calculated Total Variance and YTD values using the below DAX functions&nbsp;Total Variance = (SUM('Project Costs'[Variance])-CALCULATE(SUM('Project Costs'[Variance]),&nbsp;PREVIOUSMONTH('Project Costs'[Snapshot Date].[Date])))*-1&nbsp;YTD (January) = Total variance (January)* POC(January)&nbsp;But I am unable to find out&nbsp;MTD&nbsp;values for the table.&nbsp;MTD (January) = YTD (January) - YTD (December), this is the calculation.&nbsp;But how can write DAX function for&nbsp;MTD&nbsp;values in Power BI report? Please help me, thanks&nbsp;&nbsp;Bhavesh&nbsp;&nbsp;

Forum Discussion

Create a new column "Total Variance"

6 Replies

Related content

Creating relationships

Creating ICMs via Activator

Top 10, "Others", and Grand Total

How to remove the "Total" row

Create new table