Result of top 10 bottom 10 in a card

[mekaelj]

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

[v-shex-msft]

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

[SivaMani]

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?

[mekaelj]

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]

@mekaelj,

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

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

[mekaelj]

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]

@mekaelj,

I'm Sorry.

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

[mekaelj]

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

[v-shex-msft]

H i @mekaelj,

Please share the sample file for further testing

Regards,

Xiaoxin Sheng

[SivaMani]

@mekaelj,

Try this,

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

[mekaelj]

Hi @SivaMani,

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

Any more ideas?

Regards,

Mikael