Forum Discussion

FCF's avatar
FCF
Helper IV
7 years ago

calculating a variance

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

 

 

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      FCF
      Helper IV
      7 years ago

      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
       
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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

  • FCF's avatar
    FCF
    Helper IV
    7 years ago

    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's avatar
      FCF
      Helper IV
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi FCF ,

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

        Regards,

        Xiaoxin Sheng