calculating a variance

[FCF]

calculating a variance

hi

i am trying to get this table to calculate the variance between the following columns

Actual/sqmeter, and target

the total formula should be simple

Actual/sqmeter - target =  -2.18

the table also has a date

the target for the month is 12.5 but it is adding up all instances during the month instead of using 12.5

i am really stuck with this problem. thanks for any help

[FCF]

Hi Everyone

thanks for you help with this issue, but i believe i have not explained it properly

what I want to acheive is the difference between the Actual/sq Meter and target,

Actual/sq Meter - target= CVar

10.32 - 12.5 =  -2.18

The system is calulating the complete month for CVar as -102.18

thanks

[FCF]

Hi

thanks for everyones help with this problem. i have been able to get the results by hard coding the rowcount, but this is not an acceptible solution.

MonthTar = Related(Factor2[Expected Yr Prd])*[Factor1.Factor]/100/9

the above example looks at the number of  rows per month which is 9

what i need to to do is replace the hard coded "9" with [rows] that calculates the # of rows per month by looking at the [Actual/sq Meter]

Rows = (COUNTROWS(FILTER(J12018;[Actual/sq Meter])))

The problem is when I replace 9 with the column[rows]

MonthTar = Related(Factor2[Expected Yr Prd])*([Factor1.Factor]/100/[Rows])

i get a circular dependency  error

 error "A circular dependency was detected: J12018[MonthTar], J12018[Actual YTD/sq Meter], J12018[MonthTar]."

Thanks 

[Anonymous]

Hi @FCF ,

Please share a pbix file with some sample data for test.
Notice: do mask on sensitive data.

Regards,

Xiaoxin Sheng

[FCF]

test post

[Anonymous]

HI @FCF ,

According to your description, target field mean daily target, right? If this is a case, you can try to use calculate function to get total amount of ('Actual/sq meter' - 'target') based on current year month:

Measure =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Actual/sqmeter] ) - SUM ( Table[target] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && MONTH ( [Date] ) = MONTH ( currDate )
        )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @FCF ,

If 12.50 is a fixed value then why dont you directly subtract from the ACtaul/Sq MT. ?

Variance = 
SUM('Sheet2'[Actua/Sq Mt]) - 12.5

Don't know if this makes sense to you.

If not than give us some sample  data for different months aswell, and we should provide you the solutions.

Thanks,

Tejaswi

[FCF]

Hi

Well with persistence and help form the group i have made some progress

I am now being stopped by  this

MonthTar = Related(Factor2[Expected Yr Prd])*[Factor1.Factor]/100/9

I want to replace the number 9 at the end of the string with a column called [rows]

the column rows counts the # of rows that have a value of [Actual/sq Meter] which is in this instance 9

Rows = (COUNTROWS(FILTER(J12018;[Actual/sq Meter])))

the probem is when i replace use the column name in the string i get this error.

A circular dependency was detected: J12018[MonthTar], J12018[Actual YTD/sq Meter], J12018[MonthTar].

so yet again i am looking for direction

thanks