Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Conditional Variance and Cumulative Running Total

Hello,

 

I'm having some trouble replicating logic done in excel. My ultimate goal is to calculate variance of quotes for unique part numbers coming from multiple suppliers. I've looked through the message boards and haven't found anything that's exactly what I need. 

 

1) First I want to calculate a cumulative spend % based off the Vol Cost #1....I've done this but would like to see if there is a cleaner way.

 

2) Next, I would like to calculate variance of the quote prices by part number based on the following conditions:

- IF there is only 1 supplier, eliminate (No need for a variance calculation) 

- IF there is more than 2 suppliers, eliminate the maximum quote price from the calculation. 

 

Cost Variance with Elimination 

 

Any help is appreciated! 

Need Help

1 Reply

  • dax's avatar
    dax
    Community Support
    7 years ago

    Hi Jchin,

     

    I am not sure your requirement, did you want to show blank() like below?

    #CUSTOMERPartNumberSourcequantitycots#1vol cost #1**bleep** %
    161ABCMS-95675620#150012.562500.05
    96ABCMS-95675620#250013.567500.11
    44ABCMS-95675620#350014.572500.17
    58ABCMS-95675620#350015.57750 
    150ABCSA-95634530#150016.582500.3
    199ABCSA-95615300#150017.587500.37
    155ABCMS-95618510#150018.592500.45
    159ABCMS-95675630#150019.597500.53
    195ABCMS-95613690#150020.5102500.62
    71ABCSA-95634530#350021.5107500.71
    34ABCMS-95613400#350022.5112500.8
    120ABCMS95613270#250023.5117500.9
    33ABCMS-95613390#350024.5122501

     

    In addition, could you please explain for me "eliminate (No need for a variance calculation) ", is this a calculated column? Or the logic of this.

    By the way, below is my assuption about your description :

     

    You could try to create a calculated column in table, then create a measure like below

    samplev =
CALCULATE (
    MIN ( SAMPLE1[cots#1] ),
    FILTER (
        ALL ( SAMPLE1 ),
        SAMPLE1[PartNumber] = EARLIER ( SAMPLE1[PartNumber] )
            && 'SAMPLE1'[Source] = EARLIER ( SAMPLE1[Source] )
    )
)

    Then  create a measure for this 

    Measure =
 (
    CALCULATE (
        MAX ( SAMPLE1[samplev] ),
        ALLEXCEPT ( SAMPLE1, SAMPLE1[PartNumber] )
    )
        - CALCULATE (
            MIN ( SAMPLE1[samplev] ),
            ALLEXCEPT ( SAMPLE1, SAMPLE1[PartNumber] )
        )
)
    / CALCULATE (
        MIN ( SAMPLE1[samplev] ),
        ALLEXCEPT ( SAMPLE1, SAMPLE1[PartNumber] )
    )

     

    Best Regards,

    Zoe Zhi

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly