Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hi Community,
I have been wondering if we can create a Ranking Till Dates selected in the Slicer.
Here I have a data which has product, launch date, month and sales.
Product | Launch Date | Month | Sales |
A | Aug-20 | Sep-20 | 10 |
A | Aug-20 | Oct-20 | 8 |
A | Aug-20 | Dec-20 | 5 |
B | May-20 | Aug-20 | 4 |
B | May-20 | Sep-20 | 9 |
B | May-20 | Oct-20 | 6 |
C | Jan-21 | Feb-21 | 8 |
C | Jan-21 | Mar-21 | 5 |
C | Jan-21 | May-21 | 6 |
I was thinking if it is possible to Create a Dynamic Rank , based on the order of months, given the date selected.
Date Selected | Dec-20 | |||
Product | Sales | Launch Date | Month | RANK |
A | 10 | Aug-20 | Sep-20 | 1 |
A | 8 | Aug-20 | Oct-20 | 2 |
A | 0 | Aug-20 | Nov-20 | 3 |
A | 5 | Aug-20 | Dec-20 | 4 |
B | 0 | May-20 | Jun-20 | 1 |
B | 0 | May-20 | Jul-20 | 2 |
B | 4 | May-20 | Aug-20 | 3 |
B | 9 | May-20 | Sep-20 | 4 |
B | 6 | May-20 | Oct-20 | 5 |
if, the data between launch and month is not there, then it should take the date in order and also rank it.
Thank you
Solved! Go to Solution.
hello @Piyush_Ghungrud ,
please use this new measure:
Index expected result: =
VAR _startdate =[Begin]
VAR _monthyearbyproducttable =
CALCULATETABLE (
SUMMARIZECOLUMNS ( 'Table'[Year-Month], 'Table'[Sort] ),
FILTER ( ALL('Table'), 'Table'[Date] >= _startdate )
)
RETURN
IF (
NOT ISBLANK ( [Sales Measure] )
&& HASONEVALUE ( 'Table'[Year-Month] ),
COUNTROWS (
WINDOW (
1,
ABS,
0,
REL,
_monthyearbyproducttable,
ORDERBY ( 'Table'[Sort], ASC )
)
)
)
example 1:
example 2:
here is the powerbi file: https://1drv.ms/u/s!Ag9tIyk2ofNRjmhuYW0Ig0PdVG-9?e=pREtpE
if it solved it please accept this as a solution and thank you @Jihwan_Kim for the help
Hello @Piyush_Ghungrud i didnt quiet understand the condition of ranking, if you select december 20 on what basis should it rank?
Thank you @Anonymous for reaching me,
I want to rank months from the launch date of the product till the selected date.
As you can see, the selected month is Dec 20, till Dec 20 whatever product has launched has been displayed and ranking given to the months(Please note: Between Launch Date and Month, there could be missing Months data, but it should fill the missing months and rank those months also).
Please, let me know for further information.
Thank you 🙂
Hi @Anonymous ,
In addition to this, The different products can have the same ranks, as the ranking is based on months and the distinct product is selected.
hello @Piyush_Ghungrud sorry for late reply i got busy but i reached a place i just have the ranking im stuck on
as you can see ive reached your desired result i just have to rank it
@Piyush_Ghungrud you can find all the steps i made to reach here in the powerbi file im sorry i couldnt find a solution to ranking
powerbi file : https://1drv.ms/u/s!Ag9tIyk2ofNRjmfg4RpeqSmj2utU?e=lujztN
just make sure to change directory in powerquery of the source of Table so you can see the steps
Hi @eliasayy ,
Thank you for your efforts. I see you have got the missing months in there, as well, as per post, that's great.
Now, I am working and looking for a way to rank the Months, If you find a solution please let us know.
Thank You
hello @Piyush_Ghungrud ,
please use this new measure:
Index expected result: =
VAR _startdate =[Begin]
VAR _monthyearbyproducttable =
CALCULATETABLE (
SUMMARIZECOLUMNS ( 'Table'[Year-Month], 'Table'[Sort] ),
FILTER ( ALL('Table'), 'Table'[Date] >= _startdate )
)
RETURN
IF (
NOT ISBLANK ( [Sales Measure] )
&& HASONEVALUE ( 'Table'[Year-Month] ),
COUNTROWS (
WINDOW (
1,
ABS,
0,
REL,
_monthyearbyproducttable,
ORDERBY ( 'Table'[Sort], ASC )
)
)
)
example 1:
example 2:
here is the powerbi file: https://1drv.ms/u/s!Ag9tIyk2ofNRjmhuYW0Ig0PdVG-9?e=pREtpE
if it solved it please accept this as a solution and thank you @Jihwan_Kim for the help
Hi @Anonymous & @Jihwan_Kim ,
from your solution above, is it possible to get any specific index expected result?
Like, I wanted to have only the second(2nd) index expected result value for product A, B, and C
and the expected result would be
Index Expected Result : 2
Product | Year-Month | Sales Measure | Index Expected Result |
A | Oct-2020 | 8 | 2 |
B | Jul-2020 | 0 | 2 |
C | Mar-2021 | 5 | 2 |
Thank you 🙂
Thank you @Anonymous for helping me out.
It is what I wanted 🙂
Coming to think of it, is it possible to plot the rankings on X axis of a Line chart?
I was thinking to show the sales of products from their launch date to selected date(It will show the how the product has performed from the date of launch and all products in chart).
adding chart for reference.
Kindly ignore, the X-axis title.
In X-axis, the rankings shown(represented as months completed by product since launch). This approach involves creating Ranking in the data table, which makes it static(not dyanamic for selecting dates).
Please, let us know.
Thank you 🙂
Hello im not sure i understood
Correct me if im wrong
You have the y axis the sales measure for each product and on y axis you want everydate that was on the table by product right?
Do you want the sales to be accumulated after the launch date? Like for example product A if we select on date slicer october 2020,
On launch in august it had 10 sales then after 2 months it had 5 in september and 2 sales in october
So you want the line to be 10 then 15 then 17 respectively or 10 then 5 then 2
Hi, @Anonymous ,
On Y-axis it would be sales and X-axis it would be ranking. As you mentioned, the sales, it will not aggregate, it will be 10 then 5 then 2, this way, it will show how the product has performed since launch date.
If you could suggest any other(apart from ranking) way to get upto this result, I would be happy to know.
Thank you 🙂
I think you could try to put the sales measure i gave you in the powerbi and then add products as legend and then on the x-axis , put the year-month because they are already sorted in the table.
Hi @Anonymous ,
I have tried the way you mentioned, below is the visual that I am getting
Can we be able to mention the number of months since launch of product in the X-axis(and not the Month-Year) and start it from the '0th' value, so all the products will show emerging from zero values?
Please let me know, I will also check on the same.
Thank you 🙂
im sorry but i didnt get it do you mean that you want the first date on the xaxis to be all are 0?
Hi @Anonymous ,
Please check this, here the X-axis has started from zero and the values are also zero for all, by doing such, it shows the product just launched at that point.
Please let me know if you need any more details.
Thank you 🙂
Oh so you want the results from the answer i gave you where you add the month-year with the measure but you want the line to go from 0 up to the value on the first date of launch?
Hi @Anonymous, the line from zero and on X-axis, should show the index expected result measure.
Thank you 🙂
User | Count |
---|---|
25 | |
21 | |
20 | |
13 | |
13 |
User | Count |
---|---|
40 | |
28 | |
27 | |
23 | |
21 |