cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sshokri89
Helper I
Helper I

Ranking Customers for WTD, MTD, YTD in one single table

Hi all,

 

I have a long list of customers and I need to rank them based their SUM(SALES). I can easily create 3 tables, drag customer and sales columns and put DATE filter on each so that table1 shows current week, table2 shows MTD, and table3 shows YTD. 

 

also to show ranking value, I created this measure and used them in each table:

 

Ranking = RANKX(ALLSELECTED(Customer[C_Customer ]),CALCULATE(SUM(Sales[T_Sales])))

 

But my issue is that I need to merge the above 3 in a single table and I am not able to.  any ideas champs?

 

Cheers,

1 ACCEPTED SOLUTION

Hi @v-yalanwu-msft ,
Not actually. 
Sorry I forgot to update here but I ended up merging the three tables in Power Query and then join them based on RANK column. In that way I only Rank 1 for each period, together with their label and qty. Finally, I created ONE matrix and load them all in one table. 

My main issue on this thread was on how to merge all 3 visuals in to one. I was looking the answer in DAX but found it in M-Language. Hope it helps others as well.

View solution in original post

5 REPLIES 5
v-yalanwu-msft
Community Support
Community Support

Hi, @sshokri89 ;

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yalanwu-msft ,
Not actually. 
Sorry I forgot to update here but I ended up merging the three tables in Power Query and then join them based on RANK column. In that way I only Rank 1 for each period, together with their label and qty. Finally, I created ONE matrix and load them all in one table. 

My main issue on this thread was on how to merge all 3 visuals in to one. I was looking the answer in DAX but found it in M-Language. Hope it helps others as well.

View solution in original post

amitchandak
Super User
Super User

@sshokri89 , These are visual tables ? or Data Table ?

 

You can create measure for those, but you can create a rank on those . or create measure with measure slicer and use different measure to rank based on slicer

 

 

for week you need measure like

 

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

 

Create Rank measure on top of it

 

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

 

Measure Slicer, Single measure based on selection

https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...

 

 

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

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!

Thanks amitchandak for your reply but that's not what I'm after.

I have one table which contains all the information for my customers vs their sales. the updated measure is:

Ranking = RANKX(ALLSELECTED(TABLE[Customer ]),CALCULATE(SUM(TABLE[Sales])))


now based on that [TABLE] table, i have created separate measures for YTD, MTD, and also I have a DATE table that handles all about start of month, etc.

Now based on the above measure and the filtered I applied on each table, I manage to create the below 3 tables:

sshokri89_0-1634625370260.png


however, my purpose is to only have ONE table to show all the 3 ones above. and I believe instead of a table, I need to use a matrix and show 1 2 3 4 5  on rows, then WEEK, MTD, YTD on columns against the values for each. but I can't seem to find a way. do you have any opinion?

Hi, @sshokri89 ;

Your problem is not particularly clear and can be better understood if you have a simple example and want to output the result. One thing to note: if you want the row to show 1, 2, 3, 4, 5, you could use column not measure about rank. Another thing I don't understand is that the customers in the three tables corresponding to 1 seem to be inconsistent, so what is the logic of your merging into one table (Martix)?

vyalanwumsft_0-1634796050003.png

 

Looking forward to your reply!
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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