Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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 |
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |