Measure to rank city based on % Increase from previous year for each product category and type

[Gangsta]

I would be greatful if some one can help me create a Measure that will rank each 'City' for each 'Product' and Type, based on % increase achieved in 'Current Year' vs 'Previous Year'.

So based on the data in the table below, the Measure will allow me to rank Cities as:

Digital, Computers, 

1 New York (300%)

2 London (0 %)

3 Paris (-33%)

Digital, Laptops, 

1 London (33 %)

2 New York (-44%)

3 Paris (-83%)

Appliance Cooker, 

1 Paris (-14%)

2 London (-100%)

I have added the a '% increase Previous Year' column to the table below to show what the increase is, this column is not in my data, I only put it in for reference, should I create this column in the data sheet?

City	Type	Product	Previous Year	Current Year	%Increase Previous Year
London	Digital	Computers	1	2	0%
London	Digital	Computers	4	3	0%
London	Digital	Laptops	3	4	33%
London	Appliance	Fridge	2	6	200%
London	Appliance	Cooker	1	0	-100%
Paris	Digital	Computers	3	2	-33%
Paris	Digital	Laptops	6	1	-83%
Paris	Appliance	Cooker	4	2	-14%
Paris	Appliance	Cooker	3	4	-14%
Paris	Appliance	Hob	5	2	-60%
New York	Digital	Laptops	6	4	-44%
New York	Digital	Laptops	3	1	-44%
New York	Digital	Computers	1	4	300%
New York	Appliance	Hob	0	5	50%
New York	Appliance	Hob	4	1	50%
New York	Appliance	Cooker	0	1	#DIV/0!

[Anonymous · Accepted solution]

Hi @Gangsta 

Thanks for the reply from bhanu_gautam .

@Gangsta , the following testing is for your reference.

Create two measures as follow

%Increase Previous Year = 
VAR _sumP = CALCULATE(SUM('Table'[Previous Year]), ALLEXCEPT('Table', 'Table'[City], 'Table'[Type], 'Table'[Product]))
VAR _sumC = CALCULATE(SUM('Table'[Current Year]) ,ALLEXCEPT('Table', 'Table'[City], 'Table'[Type], 'Table'[Product]))
RETURN
DIVIDE(_sumC - _sumP, _sumP)

rank = IF(NOT(ISBLANK([%Increase Previous Year])), RANKX(ALLEXCEPT('Table', 'Table'[Type], 'Table'[Product]), [%Increase Previous Year], , DESC, Dense))

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gangsta]

@bhanu_gautam @Anonymous sorry been so busy with deadliens, I had just used a temp method as awork around.

I canc onfirm now that the solution posted works as I required and have now added the measures which you kindly provided, they are coming up with the same results, so they are 100% working for me.

Thanks again.

[bhanu_gautam]

@Gangsta ,First, create a calculated column to compute the percentage increase.

% Increase =

DIVIDE(

    [Current Year] - [Previous Year],

    [Previous Year],

    0

)

Next, create a measure to rank the cities based on the % increase.

DAX
City Rank =
VAR CurrentProduct = SELECTEDVALUE('Table'[Product])
VAR CurrentType = SELECTEDVALUE('Table'[Type])
VAR CurrentCity = SELECTEDVALUE('Table'[City])
VAR CurrentIncrease = CALCULATE(
SUM('Table'[% Increase]),
FILTER(
'Table',
'Table'[Product] = CurrentProduct &&
'Table'[Type] = CurrentType &&
'Table'[City] = CurrentCity
)
)
RETURN
RANKX(
FILTER(
ALL('Table'),
'Table'[Product] = CurrentProduct &&
'Table'[Type] = CurrentType
),
CALCULATE(
SUM('Table'[% Increase])
),
,
DESC,
DENSE
)

Use a table or matrix visual in Power BI to display the 'City', 'Type', 'Product', '% Increase', and 'City Rank' columns.

[Gangsta]

Thanks @bhanu_gautam with the way we using the % Increase column in the City Rank measure, would that not be averaging the averages once the dataset scales up to inlcde more 'Products'?

Would it be appropriate to create a % Increase Measure to create the figure needed for each City, based on Type and Prodcuct and instead of a calculated column?   If this can work, would be grateful if you can suggest the DAX for this.

That is assuming the % Increase measure be used within the City Rank Measure?

Many thanks once again.