Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I'm working on a retail dashboard and trying to create a table showing top 10 product sales, top 11-100 product sales, top 101-200 product sales, etc.
I figured out the formula for top 10 sales:
Sum({<[Article Code] = {'=Rank(sum(Net Sales))<=10'}>}[Net Sales])
But when I tried to change the formula to calculate top 11-100 product sales, I couldn't figure out the right syntax for putting in the range. Any advice would be greatly appreciated!
Thanks,
Shirley
Can you try with using double quotes inside the set analysis like this ?
Sum({<[Article Code] = {"$(=Rank(sum(Net Sales))<=10)"}>}[Net Sales])
Then can you use , Rank(sum(Net Sales))<=10 on dimension with Aggr like
IF ( Aggr( Rank(sum(Net Sales)),Dim1 , Dim2 .....) <=10 , Dimension ) with unchecking the show null / zero values ?
On the expression just keep the sum(Net Sales)
Sorry I didn't observed the "[Article Code]" in quick.
Please try like this .
Use the IF ( Aggr( Rank(sum([Net Sales])),[Article Code] ) >=10 and Aggr( Rank(sum([Net Sales])),[Article Code] )<=100 , [Article Code] ) as dimension
Use sum([Net Sales] as measure.
For testing if it works , You can cross check by adding "Aggr( Rank(sum([Net Sales])),[Article Code] ) " as additional dimension and change it to straight table.
Make another try like this , Use a Straight Table
Dimension 1 : [Article Code]
Dimension 2 : Aggr( Rank(sum([Net Sales])),[Article Code] )
Expression : sum([Net Sales]
Create a copy of this chart and change the Dimension 2 as follows
Dimension 1 : [Article Code]
Dimension 2 : IF ( Aggr( Rank(sum([Net Sales])),[Article Code] ) >=10 and Aggr( Rank(sum([Net Sales])),[Article Code] )<=100 , [Article Code] )
Expression : sum([Net Sales]
Compare the values , you would come to know the issue.