Create a new column "Total Variance"

[Bhaveshp]

Hello,

I have a table that looks this 

Table Name: Project Costs 

Columns: ID, ProjectNum, Budget, Variance, POC, Region, Country, SnapshotDate. 

My problem is I want to calculate the total variance for a particular project. for example;

 project no.    Cost Area:                 Budget            Variance          Country

  P04950         Controls Resale            221391.15        -47618.01        Australia

                       Mechanical Resale     6805080.41         430498

                       Software Resale             69206.48          24861.79

   total                                                                            24861.79

  P06271         Controls Resale            1036257            -89855             USA   

                       Mechanical Resale       2903945          -103948

                       Software Resale             198477               9660 

   total                                                                              9660

   XXXXX

1. When I tried to total the variance using the subtotal function in PowerBI, I am getting the wrong total 

Total variance = (-47618.01+430498+24861.79)= 407742, but I am getting 24861.79 which is wrong

2. I need to add new column to get the total variance for particular country, So my new report will be like below 

Country       Total Variance           YTD             MTD    

Australia      1241215                   1254            1254

USA               412542                   458444      458444

XXX

XXX

3. Also how to calculate YTD and MTD columns if we have POC and Total variance columns

Please help me, thanks in advance. 

Bhavesh

[v-xjiin-msft]

Hi @Bhaveshp,

1. To calculate the Total variance, you can try to following method:

With Calculated Column:

Col Total Variance =
CALCULATE (
    SUM ( 'Project Costs'[Variance] ),
    FILTER (
        'Project Costs',
        'Project Costs'[ProjectNum] = EARLIER ( 'Project Costs'[ProjectNum] )
    )
)

With Measure:

Mea Total Variance =
CALCULATE (
    SUM ( 'Project Costs'[Variance] ),
    FILTER (
        ALL ( 'Project Costs' ),
        'Project Costs'[ProjectNum] = MAX ( 'Project Costs'[ProjectNum] )
    )
)

2. To calculate the Total Variance for particular country. Just change above filter from 'Project Costs'[ProjectNum] to 'Project Costs'[Country].

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. 

By the way, your source table has more columns than your sample data like POC. So please share us more detailed sample data which can help us get your desired result.

Thanks,
Xi Jin.

[Bhaveshp]

Hi @v-xjiin-msft,

Thanks for your response. I tried to calculate Total Variance and YTD values

Total Variance = (SUM('Project Costs'[Variance])-CALCULATE(SUM('Project Costs'[Variance]), PREVIOUSMONTH('Project Costs'[Snapshot Date].[Date])))*-1

YTD (January) = Total variance (January)* POC(January)

But I am unable to find out MTD values for the table. 

MTD (January) = YTD (January) - YTD (December), this is the calculation.

But how can measure MTD values in Power BI report? Please help me, thanks 

Bhavesh

[v-xjiin-msft]

Hi @Bhaveshp,

Could you please share us your pbix file, or your excel file with detailed sample data and the desired result if possible? Otherwise, it is hard for us to understand your requirement clearly.

Thanks,

Xi Jin.

[Bhaveshp]

Hello,

I have a table 'Project Costs' shown below,

I have calculated Total Variance and YTD values using the below DAX functions

Total Variance = (SUM('Project Costs'[Variance])-CALCULATE(SUM('Project Costs'[Variance]), PREVIOUSMONTH('Project Costs'[Snapshot Date].[Date])))*-1

YTD (January) = Total variance (January)* POC(January)

But I am unable to find out MTD values for the table. 

MTD (January) = YTD (January) - YTD (December), this is the calculation.

But how can write DAX function for MTD values in Power BI report? Please help me, thanks

Bhavesh 

[Bhaveshp]

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 

[Bhaveshp]

Hi Xi Jin, 

Thanks for your reply. I calculated total variance using the column 

Total Variance = CALCULATE(SUM('Project Costs'[Variance_Num]),FILTER('Project Costs', 'Project Costs'[Project Number] = EARLIER(('Project Costs'[Project Number]))))

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.

My sample data is shown below.