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

[sshokri89]

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,

[v-yalanwu-msft]

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.

[sshokri89 · 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.

[amitchandak]

@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

[sshokri89]

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:

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?

[v-yalanwu-msft]

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

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.