Help Building Query

[Anonymous]

Brand	ModelName	ModelNumber	SalesPrice	Logic	Expected_output
A	ABC	123	25	A'Price/A'Price	1
B	ABC	123	30	A'Price/B'Price	1.2
C	ABC	123	25.4	A'Price/C'Price	1.016
D	ABC	123	23	A'Price/D'Price	0.92
E	ABC	123	26	A'Price/E'Price	1.04
A	DEF	456	50	A'Price/A'Price	1
B	DEF	456	48	A'Price/B'Price	0.96
C	DEF	456	55	A'Price/C'Price	1.1
D	DEF	456	53	A'Price/D'Price	1.06
E	DEF	456	45	A'Price/E'Price	0.9

I have this sample data with me i have to calculate ratio as "A'Price/Different brand price" for that particular modelnumber as mentioned in above data.i have also shown expected output above..please help me on it..

[v-haibl-msft]

@Anonymous

Please try to create a calculated column with following expression.

Expected_output = 
Table1[SalesPrice]
    / CALCULATE (
        MAX ( Table1[SalesPrice] ),
        FILTER (
            ALL ( Table1 ),
            Table1[ModelName] = EARLIER ( Table1[ModelName] )
                && Table1[Brand] = "A"
        )
)

Best Regards,

Herbert

[Anonymous]

Brand	ModelName	ModelNumber	SalesPrice	Logic	Expected_output
A	ABC	123	25	A'Price/A'Price	1
B	ABC	123	30	A'Price/B'Price	1.2
D	DEF	456	53	A'Price/D'Price	1.06
E	DEF	456	45	A'Price/E'Price	0.9
E	ABC	123	26	A'Price/E'Price	1.04
A	DEF	456	50	A'Price/A'Price	1
B	DEF	456	48	A'Price/B'Price	0.96
C	DEF	456	55	A'Price/C'Price	1.1
C	ABC	123	25.4	A'Price/C'Price	1.016
D	ABC	123	23	A'Price/D'Price	0.92

whats the logic in case of same data is randomly placed as shown above..in this case EARLIER function will store previous value and that leads to wrong calculation beacuse in such case for brand "A" it should always "1" but giving different values.

colud please help me out....

thanks in advance Herbert.

[v-haibl-msft]

@Anonymous

Using MAX() function here is just to get an aggregate value. We can also use MIN() here since there is only one A in each Model.

If the data is randomly placed, the calculated column should still works as below.

Best Regards,

Herbert

[Anonymous]

Completely sorted.....thank you very much herbert... 

it was really great help for my project....thank you so much again

[Anonymous]

Hello Herbert,

Can i know why you use MAX() function for sales price...could not collect it....

Thanks,

Rahul M