Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Everyone,
I need help in picking up top sales in every month using rank .
My current Data
Category | pricing_date | Sale amount | Rank By Sales Amount |
Bench | 11/15/2011 | -4.38 | 1 |
Bench | 11/14/2011 | -4.29 | 2 |
Bench | 11/16/2011 | -4.01 | 3 |
Bench | 12/10/2014 | -3.73 | 4 |
Bench | 12/11/2014 | -2.98 | 5 |
Bench | 12/19/2012 | -2.88 | 6 |
Bench | 11/28/2014 | -2.85 | 7 |
Bench | 12/18/2012 | -2.55 | 8 |
Bench | 11/26/2014 | -2.48 | 9 |
Bench | 12/8/2014 | -2.48 | 10 |
For this i need Rank baseed on Day and month so that i can filter top sales by month .
Category | pricing_date | Sale amount | Rank By Sales Amount | Rank for sales by month |
Bench | 11/15/2011 | -4.38 | 1 | 1 |
Bench | 11/14/2011 | -4.29 | 2 | 2 |
Bench | 11/16/2011 | -4.01 | 3 | 3 |
Bench | 12/10/2014 | -3.73 | 4 | 1 |
Bench | 12/11/2014 | -2.98 | 5 | 2 |
Bench | 12/19/2012 | -2.88 | 6 | 1 |
Bench | 11/28/2014 | -2.85 | 7 | 1 |
Bench | 12/18/2012 | -2.55 | 8 | 2 |
Bench | 11/26/2014 | -2.48 | 9 | 2 |
Bench | 12/8/2014 | -2.48 | 10 | 3 |
Thanks
Sravan
Solved! Go to Solution.
Hi @Anonymous,
Then we can create a calculated column to work on it.
minsales = var minsal =CALCULATE(MIN(Table1[Sale amount]),ALLEXCEPT(Table1,Table1[Yearmonth])) return IF(Table1[Sale amount]=minsal,1,0)
For more details, please check the pbix as attached.
Regards,
Frank
Hi @Anonymous,
Does that make sense? If so, kindly mark the answer as solution to close the case.
Regards,
Frank
Hi @Anonymous,
I made one sample for your reference. We can create a measure to work on it.
Measure = var minsales = CALCULATE(MIN(Table1[Sale amount]),ALLEXCEPT(Table1,Table1[Yearmonth])) return IF(MAX(Table1[Sale amount])=minsales,1,0)
Then create a table visual and make it filterd by the measure as below.
For more details, please check the pbix as attached.
Regards,
Frank
Thanks for the help @v-frfei-msft but when i remove year month column it's loosing its ranking.
Hi @Anonymous,
Does that make sense? If so, kindly mark the answer as solution to close the case.
Regards,
Frank
Hi @Anonymous,
Then we can create a calculated column to work on it.
minsales = var minsal =CALCULATE(MIN(Table1[Sale amount]),ALLEXCEPT(Table1,Table1[Yearmonth])) return IF(Table1[Sale amount]=minsal,1,0)
For more details, please check the pbix as attached.
Regards,
Frank
@Anonymous
Try this calculated column
It works with sample data
Rank by Month = RANKX(Filter(Table1,[Category]=EARLIER([Category])
&&[pricing_date].[Month]=earlier([pricing_date].[Month])&&
[pricing_date].[Year]=earlier([pricing_date].[Year])),[Sale amount],,ASC,Dense)
Hi,
Thanks for hepling me out,
When i use the calculated column it give's me the follwing error .
A circular dependency was detected: table[Rank by Month].
In the sample data the sale amount is a calculated mesure .
Is there a may to create a RankBy month as a measure.
Thanks
Sravan
Hi Everyone,
I need help in picking up top sales in every month using rank .
My current Data
Category | pricing_date | Sale amount | Rank By Sales Amount |
Bench | 11/15/2011 | -4.38 | 1 |
Bench | 11/14/2011 | -4.29 | 2 |
Bench | 11/16/2011 | -4.01 | 3 |
Bench | 12/10/2014 | -3.73 | 4 |
Bench | 12/11/2014 | -2.98 | 5 |
Bench | 12/19/2012 | -2.88 | 6 |
Bench | 11/28/2014 | -2.85 | 7 |
Bench | 12/18/2012 | -2.55 | 8 |
Bench | 11/26/2014 | -2.48 | 9 |
Bench | 12/8/2014 | -2.48 | 10 |
For this i need Rank baseed on Day and month so that i can filter top sales by month .
Category | pricing_date | Sale amount | Rank By Sales Amount | Rank for sales by month |
Bench | 11/15/2011 | -4.38 | 1 | 1 |
Bench | 11/14/2011 | -4.29 | 2 | 2 |
Bench | 11/16/2011 | -4.01 | 3 | 3 |
Bench | 12/10/2014 | -3.73 | 4 | 1 |
Bench | 12/11/2014 | -2.98 | 5 | 2 |
Bench | 12/19/2012 | -2.88 | 6 | 1 |
Bench | 11/28/2014 | -2.85 | 7 | 1 |
Bench | 12/18/2012 | -2.55 | 8 | 2 |
Bench | 11/26/2014 | -2.48 | 9 | 2 |
Bench | 12/8/2014 | -2.48 | 10 | 3 |
Thanks
Sravan
Hi @Anonymous
As a MEASURE try this
Measure_Rank by Month = RANKX ( FILTER ( ALL ( Table1 ), [Category] = SELECTEDVALUE ( Table1[Category] ) && MONTH ( [pricing_date] ) = MONTH ( SELECTEDVALUE ( Table1[pricing_date] ) ) && YEAR ( [pricing_date] ) = YEAR ( SELECTEDVALUE ( Table1[pricing_date] ) ) ), [SaleAmount], , ASC, DENSE )
Hi @Zubair_Muhammad,
Its Taking too much time to excute it gave memory out of exception error .
Actually i need the final output as top 4 sales with non overlapping dates
Category | pricing_date | Sale amount | Rank By Sales Amount | Rank for sales by month |
Bench | 11/15/2011 | -4.38 | 1 | 1 |
Bench | 12/10/2014 | -3.73 | 4 | 1 |
Bench | 12/19/2012 | -2.88 | 6 | 1 |
Bench | 11/28/2014 | -2.85 | 7 | 1 |
Thanks for Help
Sravan
Hi @Anonymous
As a MEASURE try this
Measure_Rank by Month = RANKX ( FILTER ( ALL ( Table1 ), [Category] = SELECTEDVALUE ( Table1[Category] ) && MONTH ( [pricing_date] ) = MONTH ( SELECTEDVALUE ( Table1[pricing_date] ) ) && YEAR ( [pricing_date] ) = YEAR ( SELECTEDVALUE ( Table1[pricing_date] ) ) ), [SaleAmount], , ASC, DENSE )
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |