DAX for top 2 out of 4 weeks

[Minakshi]

Hi All,

I have a table like this 

shop name	customer name	week	purchace
Vikas Gen Store	Rohit	7/9/2020	100
Vikas Gen Store	Mohit	7/9/2020	150
Vikas Gen Store	Sohan	14/9/2020	200
Vikas Gen Store	Mohan	14/9/2020	10
Vikas Gen Store	Vikas	21/9/2020	250
Vikas Gen Store	Rohit	29/9/2020	10
Vikas Gen Store	Rohan	21/9/2020	20
Vikas Gen Store	Vikas	29/9/2020	20

As a result I expect, top 2 week (descending order) those are having individual maximum purchage. 

It gives the following table as output

Vikas Gen Store	Sohan	14/9/2020	200
Vikas Gen Store	Vikas	21/9/2020	250

and finally gives 

Vikas Gen store

450

Kindly help me with DAX expression.

Thanks in advance

[Fowmy]

@Minakshi 

Use this measure please :  

Shop	Top 2 Total
Vikas Gen Store	450

Top 2 Total = 

CALCULATE(
    SUM(Table4[purchace]),
    TOPN( 2 , Table4 , Table4[purchace] ,DESC)
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[amitchandak]

@Minakshi , Try like

//2 measures
PO = SUM(Top2[purchace])
Top 2 Rank = CALCULATE([PO],TOPN(2,all(Top2),[PO],DESC),VALUES(Top2))

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

[Minakshi]

@amitchandak , @Fowmy  it seems top 2 doesn't work when there are 2 purchases with same amount in same week. It adds them up. I need only one purchase out of two even if they are same.

Could you please help me, with this.

[Fowmy]

@Minakshi

Can you try this revised measure?

Top 2 Total = 
SUMX(
    TOPN( 
        2 ,  
        SUMMARIZE( Table4 , Table4[shop name],Table4[week], "_Max", MAX(Table4[purchace])), 
        [_Max] ,DESC,Table4[week]
    ), 
    [_Max]
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube Linkedin

[Minakshi]

Thank you so much for your efforts @Fowmy ., @amitchandak . But my problem still unresolved, in case where there are no visits except 2 visits in same week. It adds up both visit but I want top 1 visit out of 2 visits from one week.

Thanks

[Fowmy]

@Minakshi 

Can you prepare a sample with the expected result including the scenarios that you are explaining here? 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Minakshi · Accepted solution]

@Fowmy I solved the issue with groupby with Week and taking max of it. Thanks once again for your help

[amitchandak]

@Minakshi ,

The first measure remains the same. Use these two. The third one is the one you need in visual 

PO = SUM(Top2[purchace])
Top2 Rank = rankx(ALL(Top2),[PO],,DESC,Dense) + RAND()/1000
Top 2 Shop Rank = CALCULATE([PO],TOPN(2,all(Top2),[Top2 Rank],ASC),VALUES(Top2))