Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Top 1 sales by month

Hi Everyone,

 

I need help in picking up top sales in every month  using rank .

 

My current Data 

Categorypricing_dateSale amountRank By Sales Amount
Bench11/15/2011-4.381
Bench11/14/2011-4.292
Bench11/16/2011-4.013
Bench12/10/2014-3.734
Bench12/11/2014-2.985
Bench12/19/2012-2.886
Bench11/28/2014-2.857
Bench12/18/2012-2.558
Bench11/26/2014-2.489
Bench12/8/2014-2.4810

 

 For this i need Rank  baseed on Day and month so that i  can filter top sales by month .

Categorypricing_dateSale amountRank By Sales AmountRank for sales by month
Bench11/15/2011-4.3811
Bench11/14/2011-4.2922
Bench11/16/2011-4.0133
Bench12/10/2014-3.7341
Bench12/11/2014-2.9852
Bench12/19/2012-2.8861
Bench11/28/2014-2.8571
Bench12/18/2012-2.5582
Bench11/26/2014-2.4892
Bench12/8/2014-2.48103

 

Thanks

Sravan 

 

2 ACCEPTED SOLUTIONS

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)

2.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Hi @Anonymous,

 

Does that make sense? If so, kindly mark the answer as solution to close the case.


Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

11 REPLIES 11
v-frfei-msft
Community Support
Community Support

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.

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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)

2.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Zubair_Muhammad
Community Champion
Community Champion

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

rankbymonth.png

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

 

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 

Anonymous
Not applicable

Hi Everyone,

 

I need help in picking up top sales in every month  using rank .

 

My current Data 

Categorypricing_dateSale amountRank By Sales Amount
Bench11/15/2011-4.381
Bench11/14/2011-4.292
Bench11/16/2011-4.013
Bench12/10/2014-3.734
Bench12/11/2014-2.985
Bench12/19/2012-2.886
Bench11/28/2014-2.857
Bench12/18/2012-2.558
Bench11/26/2014-2.489
Bench12/8/2014-2.4810

 

 For this i need Rank  baseed on Day and month so that i  can filter top sales by month .

Categorypricing_dateSale amountRank By Sales AmountRank for sales by month
Bench11/15/2011-4.3811
Bench11/14/2011-4.2922
Bench11/16/2011-4.0133
Bench12/10/2014-3.7341
Bench12/11/2014-2.9852
Bench12/19/2012-2.8861
Bench11/28/2014-2.8571
Bench12/18/2012-2.5582
Bench11/26/2014-2.4892
Bench12/8/2014-2.48103

 

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
)

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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 

Categorypricing_dateSale amountRank By Sales AmountRank for sales by month
Bench11/15/2011-4.3811
Bench12/10/2014-3.7341
Bench12/19/2012-2.8861
Bench11/28/2014-2.8571

 

Thanks for Help

Sravan 

@Ashish_Mathurcould you help @Anonymous

 

 


Regards
Zubair

Please try my custom visuals

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
)

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.