Power pivot second biggest value based on filter

[Farchurch]

Hi, 

I have a table and there i have certain products. Some of the products are interchangeable with eachother depending on the packsize. i made a key to connect the products and now it want to show in colums each avaialbe packsize for the key from highst to lowist packsize. I made this formula to find the biggest packsize in power pivot. How can i show the second biggest in a different colum? 

biggest value =CALCULATE(MAX([packsize]);ALLEXCEPT('table1';table1[Key]))

i tried to add the biggest value to the formula above in combination with < but couldnt get it to work.

regards Johan

[Kaly · Accepted solution]

Hi @Farchurch ,

According to your description, here's my solution. Create a calculated column.

Column =
MAXX (
    FILTER (
        'Table1',
        'Table1'[Key] = EARLIER ( 'Table1'[Key] )
            && 'Table1'[Packsize] <> 'Table1'[biggest value]
    ),
    'Table1'[Packsize]
)

Get the correct result.

Best Regards,

Kaly

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

[Farchurch]

@amitchandak 

Maby a weird question put how do i upload a pbix or table, if i drag and drop i get an error.

Packsize	value 1	value 2	value 3	value 4	value 5	value 6	value 7	Key	Key not for now	key not for now	Biggest value	second biggest value
12	ST	1	BAG	12	ST	18880	26069	18880STBAG	18880STBAG12ST	2606912STST	204
24	ST	2	BAG	12	ST	18880	26069	18880STBAG	18880STBAG24ST	2606924STST	204
30	ST	1	BAG	30	ST	18880	32548	18880STBAG	18880STBAG30ST	3254830STST	204
30	ST	2	BAG	15	ST	18880	104193	18880STBAG	18880STBAG30ST	10419330STST	204
48	ST	4	BAG	12	ST	18880	26067	18880STBAG	18880STBAG48ST	2606748STST	204
48	ST	4	BAG	12	ST	18880	26069	18880STBAG	18880STBAG48ST	2606948STST	204
96	ST	8	BAG	12	ST	18880	26069	18880STBAG	18880STBAG96ST	2606996STST	204
96	ST	8	BAG	12	ST	18880	115479	18880STBAG	18880STBAG96ST	11547996STST	204
105	ST	7	BAG	15	ST	18880	9155	18880STBAG	18880STBAG105ST	9155105STST	204
105	ST	3	BAG	35	ST	18880	104193	18880STBAG	18880STBAG105ST	104193105STST	204
204	ST	17	BAG	12	ST	18880	26069	18880STBAG	18880STBAG204ST	26069204STST	204
204	ST	17	BAG	12	ST	18880	115479	18880STBAG	18880STBAG204ST	115479204STST	204

[amitchandak]

@Farchurch , You can create a rank and filter use visual level filter for Rank =2

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.