Conditional Variance and Cumulative Running Total

[Anonymous]

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! 

[dax]

Hi Jchin,

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

#	CUSTOMER	PartNumber	Source	quantity	cots#1	vol cost #1	**bleep** %
161	ABC	MS-95675620	#1	500	12.5	6250	0.05
96	ABC	MS-95675620	#2	500	13.5	6750	0.11
44	ABC	MS-95675620	#3	500	14.5	7250	0.17
58	ABC	MS-95675620	#3	500	15.5	7750
150	ABC	SA-95634530	#1	500	16.5	8250	0.3
199	ABC	SA-95615300	#1	500	17.5	8750	0.37
155	ABC	MS-95618510	#1	500	18.5	9250	0.45
159	ABC	MS-95675630	#1	500	19.5	9750	0.53
195	ABC	MS-95613690	#1	500	20.5	10250	0.62
71	ABC	SA-95634530	#3	500	21.5	10750	0.71
34	ABC	MS-95613400	#3	500	22.5	11250	0.8
120	ABC	MS95613270	#2	500	23.5	11750	0.9
33	ABC	MS-95613390	#3	500	24.5	12250	1

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