cancel
Showing results for
Did you mean:
Highlighted
Helper III

## DAX for top 2 out of 4 weeks

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper III

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

8 REPLIES 8
Highlighted
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))``````

Proud to be a Super User!

Highlighted
Community Champion

@Minakshi

 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 🙂

Highlighted
Helper III

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

Highlighted
Super User IV

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

Proud to be a Super User!

Highlighted
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 🙂

Highlighted
Helper III

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

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 🙂

Highlighted
Helper III

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors