Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@Fowmy I solved the issue with groupby with Week and taking max of it. Thanks once again for your help
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
@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 🙂
⭕ 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
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 🙂
⭕ 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
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |