cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

DAX for top 2 out of 4 weeks

Hi All,

 

I have a table like this 

shop namecustomer nameweekpurchace
Vikas Gen StoreRohit7/9/2020100
Vikas Gen StoreMohit7/9/2020150
Vikas Gen StoreSohan14/9/2020200
Vikas Gen StoreMohan14/9/202010
Vikas Gen StoreVikas21/9/2020250
Vikas Gen StoreRohit29/9/202010
Vikas Gen StoreRohan 21/9/202020
 Vikas Gen StoreVikas29/9/202020
    

 

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 StoreSohan 14/9/2020200
Vikas Gen Store Vikas21/9/2020250

 

and finally gives 

Vikas Gen store 450

 

Kindly help me with DAX expression.

Thanks in advance

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

View solution in original post

8 REPLIES 8
Highlighted
Super User IV
Super User IV

@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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Champion
Community Champion

@Minakshi 

Use this measure please :  


ShopTop 2 Total
Vikas Gen Store450



 

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

 

Highlighted

@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.

Highlighted

@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))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Champion
Community Champion

@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

Highlighted

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

Highlighted
Community Champion
Community Champion

@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

Highlighted

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors