topic Re: Measure to rank city based on % Increase from previous year for each product category and type in DAX Commands and Tips

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

Gangsta — Tue, 03 Sep 2024 11:12:10 GMT

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!

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

bhanu_gautam — Tue, 03 Sep 2024 12:53:38 GMT

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

% Increase =

DIVIDE(

[Current Year] - [Previous Year],

[Previous Year],

)

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.

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

Gangsta — Tue, 03 Sep 2024 14:38:11 GMT

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.

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

Anonymous — Thu, 05 Sep 2024 07:28:25 GMT

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.

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

Gangsta — Mon, 23 Sep 2024 11:12:29 GMT

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.