dax query

[Arjun_reddy]

Hi Community team,

I need you help on build dax query for second largest retail channel on total sales
I am sharing the code for first largest retail channel  on total sales 

1largest_TotalChannel =

VAR MaxTotal = MAX(Tostitos_MP[TOTAL OCCASIONS])

VAR Maxyear=MAX(Tostitos_MP[YEAR])

VAR CHANNEL_OCC=

    CALCULATE(

        VALUES(Tostitos_MP[RETAIL CHANNEL]),

        Tostitos_MP[TOTAL OCCASIONS] = MaxTotal,Tostitos_MP[YEAR]=Maxyear

    )

    RETURN

    CHANNEL_OCC

Please check the code and modify for second largest retail channel on total occasions 

Thanks,

Arjun Reddy

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RihMKgrmog1QqoECj?e=OQNfR6

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

[Ahmedx]

Share sample pbix file to help you.

[Arjun_reddy]

Please find the below data table

category	year	sales
chairs	2021	5000
tables	2021	6000
bulbs	2022	6000
sofa	2022	4500
chairs	2022	8000
chairs	2021	6000
chairs	2021	2800
sofa	2022	4700
bulbs	2022	2000
sofa	2021	5500
tables	2021	6000
tables	2022	3400
bulbs	2021	4000

[Ahmedx]

you can show what result you expect

[Arjun_reddy]

result should be have as mentioned rounded yellow color in snap

[Ahmedx]

Is this what you are looking for?

[Ahmedx]

or this&

[Arjun_reddy]

Hi  Ahmedx,

not that solution, my query is i want to get the category on which is the second highest sales in category on max year, In the data , chairs is having second highest sales, please write the query on my concern.
result should get as: chairs contributed with sales value

[Arjun_reddy]

Hi Ahmedx,

I am unable to add power bi and excel file, please find the below attachment.

Thanks,

Arjun Reddy.