cancel
Showing results for
Did you mean:
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
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

## 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))```

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: | |
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

Senior Member

## Re: Result of top 10 bottom 10 in a card

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

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

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

Senior Member

I'm Sorry.

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."

Senior Member

## Re: Result of top 10 bottom 10 in a card

Try this,

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

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