cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mekaelj Regular Visitor
Regular Visitor

Result of top 10 bottom 10 in a card

Hi guys,

 

I have created a measure to be able to show the top 10 and bottom 10 items in our inventory based on a comparison to sales from last year. This is done with the following measures:

 

Sales Quantity = SUM(Salesdata[SalesQty])

Sales Quantity Last Year = CALCULATE([Sales Quantity];PARALLELPERIOD('Date'[Date];-12;MONTH))

Sales Quantity Diff current year last year = [Sales Quantity] - [Sales Quantity Last Year]

 

RankX Quantity Bottom = RANKX(FILTER(ALLSELECTED(ItemDatabase[Productdescription]);[Sales Quantity Diff current year last year]);[Sales Quantity Diff current year last year];;ASC;Dense)

RankX Quantity Top = RANKX(FILTER(ALLSELECTED(ItemDatabase[Productdescription]);[Sales Quantity Diff current year last year]);[Sales Quantity Diff current year last year];;DESC;dense)

RankX Quantity Final = IF(OR([RankX Quantity Top]<11;[RankX Quantity Bottom]<11);1;0)

 

So to get the graphs to show only the top 10 and bottom 10 I have to set the RankX Quantity Final as a visual filter and then chose show items when the value is = 1.

 

Now, I want to create a card that shows the difference between the top 10 and bottom 10 in terms of quantity sold. However, I can't apply the measure I've created to a card and filter on only items where the value is 1. How would I go about creating this card?

 

Cheers,

 

Mikael

10 REPLIES 10
SivaMani Senior Member
Senior Member

Re: Result of top 10 bottom 10 in a card

Hi @mekaelj,

 

Correct me if I'm wrong,

 

Why don't you have two measures(Top 10 Sum and Bottom 10 Sum) to sum up the top 10 and bottom 10 items?

 

 

Community Support Team
Community Support Team

Re: Result of top 10 bottom 10 in a card

HI @mekaelj,

 

According to your description, you want to get diff of same rank from different sort models, right?

If this is a case, you can refer to below formula if it suitable for your requirement.

 

Measure:

Tag = IF([DESC]<=10||[ASC]<=10,1,0)

Diff of ASC and DESC = var top_index=CALCULATE([ASC],FILTER(ALL('Sample'),'Sample'[Index]=MAX('Sample'[Index]))) return MAX('Sample'[Amount])-CALCULATE(SUM('Sample'[Amount]),FILTER(ALL('Sample'),[DESC]=top_index))

3.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
mekaelj Regular Visitor
Regular Visitor

Re: Result of top 10 bottom 10 in a card

Hi @SivaMani,

 

How would I create those measures? If I could create one top 10 measure and one bottom 10 measure I could just subtract them from each other and get what I want.

 

Regards,

 

Mikael

SivaMani Senior Member
Senior Member

Re: Result of top 10 bottom 10 in a card

@mekaelj,

 

Top 10 = CALCULATE(SUM(...),[RankX Quantity Top]<11)

Bottom 10 = CALCULATE(SUM(...),[RankX Quantity Top]>10)

mekaelj Regular Visitor
Regular Visitor

Re: Result of top 10 bottom 10 in a card

Hi @SivaMani,

 

I can't use the sum function as the way I calculate the sales quantity and revenue is with measures. Any other way I could do this?

 

Regards,

 

Mikael

SivaMani Senior Member
Senior Member

Re: Result of top 10 bottom 10 in a card

@mekaelj,

 

I'm Sorry.

 

Just remove SUM() function just add the measure instead.

mekaelj Regular Visitor
Regular Visitor

Re: Result of top 10 bottom 10 in a card

@SivaMani

 

Like this? 

 

Bottom 10 quantity = CALCULATE([Sales Quantity Diff current year last year];[RankX Quantity Bottom]<10)

 

That gives me the following error "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

SivaMani Senior Member
Senior Member

Re: Result of top 10 bottom 10 in a card

@mekaelj,

 

Try this,

 

Bottom 10 quantity = CALCULATE([Sales Quantity Diff current year last year],FILTER(TableName,[RankX Quantity Bottom]<10))

 

 

 

 

mekaelj Regular Visitor
Regular Visitor

Re: Result of top 10 bottom 10 in a card

Hi @SivaMani,

 

That's still not working. It's giving me "Can't display the visual".

 

Any more ideas?

 

Regards,

 

Mikael