cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Minakshi
Resolver I
Resolver I

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

@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
Fowmy
Super User
Super User

@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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

amitchandak
Super User
Super User

@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 !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@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

@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 !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors