Forum Discussion
Power pivot second biggest value based on filter
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
Hi Anonymous ,
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.
3 Replies
KalyResolver II
Hi Anonymous ,
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.
- amitchandak
Super User
Anonymous , 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. 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
