Forum Discussion

UKNSI-Powerbi's avatar
UKNSI-Powerbi
Frequent Visitor
3 years ago
Solved

Row level variance

At the moment I have a table of customer cumulations grouped by  Cust/Seq/Contract/Part/Date/Release/YYYYMM   Cust Seq Contract Part Date Release Rev YYYYMM CUMULATION CUST1     1 0...
Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi UKNSI-Powerbi 

    you may try to add a calculated column like this:

    Variance2 = 
VAR _date = [Date]
VAR _cumpre =
CALCULATE(
    SUM(TableName[CUMULATION]),    
    ALLEXCEPT(TableName, TableName[Cust], TableName[Seq], TableName[Contract], TableName[Part], TableName[YYYYMM]),
    TableName[Date] = _date-1
)
RETURN
DIVIDE( [CUMULATION] - _cumpre, _cumpre)

     

    tried and it worked like this:

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

This calculated column formula works

=DIVIDE([CUMULATION],LOOKUPVALUE(data[CUMULATION],data[Date],CALCULATE(MAX(data[Date]),FILTER(data,data[Cust]=EARLIER(data[Cust])&&data[Seq]=EARLIER(data[Seq])&&data[Contract]=EARLIER(data[Contract])&&data[Part]=EARLIER(data[Part])&&data[Date]<EARLIER(data[Date]))),data[Cust],data[Cust],data[Seq],data[Seq],data[Contract],data[Contract],data[Part],data[Part],data[YYYYMM],data[YYYYMM]))-1

Hope this helps.

UKNSI-Powerbi's avatar
UKNSI-Powerbi
Frequent Visitor
3 years ago

Using the same table I would like to find variance in a different way: net variance from a previous Rel N. and month(s). Here an example of calculation:

 

03 June 2022

Variance 202206 = (H6-H5)-P6

Variance 202207 = (I6-I5)-Q6-P6

Variance 202208 = (J6-J5)-R6-Q6-P6

Variance 202209 = (K6-K5)-S6-R6-Q6-P6

Variance 202210 = (L6-L5)-T6-S6-R6-Q6-P6

Variance 202211 = (M6-M5)-U6-T6-S6-R6-Q6-P6

Variance 202212 = (N6-N5)-V6-U6-T6-S6-R6-Q6-P6

 

Here is like it looks like.

 

 

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    I have no idea of what you want.  I am as clear as mud.