Ranking by Different Levels Category in Different Columns Same Visual

[rkapukaya]

I've a table visual display sales and growing percentage (current year vs previous year) by dimensions (Store, manager, supervisor)

Store table filters -> fact table (1 to n)

In the same visual i want to show rank by sales in all stores, rank by growing within each manager's own stores, rank by growing each supervisor's own stores. I'm stucked with this issue. how can i do that.

[Anonymous]

Category	Output_1	Output_2
A	1	3
A	1	3
A	1	3
B	2	2
B	2	2
C	3	2
C	3	2
A	4	6
A	4	6
A	4	6
A	4	6
A	4	6
A	4	6

Is there any way to get either output_1 or output_2 in columns.

In output_1, I want to rank the categories with different rank every time it appears after break. For e.g., In this case A was ranked 1 initially and after break it was ranked 4. Whereas, in ouput_2, I am trying to calculate the count of A , which should change with every break. Like for, first 3 consecutive As it should count 3 and thereafter for last consecutive A's it should count 6.

[rkapukaya]

@amitchandak 
Thanks for your response,

I'm okay with measure calculation. My table has both current year sales and previos year sales. I just calculate growing percentage on powerbi.

I watched your youtube video. it's helpful, yet i want 3 RANK same time in one table visual.
1. Rank : All stores rank by Current year sales. (without any manager or supervisor)
2. Rank : store rank by growing (calc measure) for each manager's own stores.
On my screenshot, Column G shows this. man-1 and man-2, each of them has 4 stores and i ranked them by own stores grown percentage.
3. Rank : store rank by growing (calc measure) for each supervisor's own stores.
on my screenshot, column H show this. Sup-2 and Sup-1, each of them has 2 stores and i ranked them by own stores grown percentage.

According to your youtube and other refer links. they all have just one rank column. I cannot to this.

[Anonymous · Accepted solution]

Hi @rkapukaya,

I'd like to suggest you take a look at the following blog to know how to use 'if statement' to check the current row contents level, then you can write multiple ranking formulas of different hierarchy levels. (notice: they may not work on the total levels)

Clever Hierarchy Handling in DAX 

Regards,

Xiaoxin Sheng

[amitchandak]

@rkapukaya , Not very clear. But you can use time intelligence and date table go get measure like this

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"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

[amitchandak]

@rkapukaya , For YTD refer above.

for Rank refer

For Rank Refer these links

https://www.youtube.com/watch?v=DZb_6j6WuZ0
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...