cancel
Showing results for
Did you mean:
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
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
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 🙂

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!

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.

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 🙂

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

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 🙂

Helper III

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

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!

Announcements

Happy New Year from Power BI

This is a must watch for a message from Power BI!