Normalized Growth Rates

[mwilliamsjr]

Hi Forum, 

I need your help. I am trying to find a way to normalize growth rates, that is scaling growth rates relative to a minimum Y axis of 0 and a maximum Y axis of 1. The formula for this is as follows:

[y axis min] + ( [Min Growth Rate] ) * ([y axis max]- [y axis min] / ([Max Growth Rate]-[Min Growth Rate])

For this to work with the filter product dimensions below, how do I access the min and max growth rates to carry  out the noramize growth measure above:

- Product Name

- Product Line

- Product Family

- Product Type

The data model is as follows:

Dim Table: Date (typical calendar table)

Dim Table: Products

- Product Name

- Product Line

- Product Family

- Product Type

Fact Table: Sales

-Product

-Month (MM/01/YYYY)

-Qty

-Rev

[v-shex-msft]

Hi @mwilliamsjr,

Any update on this? Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[mwilliamsjr]

Hi All, 

Okay below is sample data:

Sales	2019	2020	2021	2022
Paper	$     1,088	$     1,779	$     2,285	$     2,304
Gold	$     1,314	$     1,082	$     1,453	$     1,601
Silver	$   21,719	$   21,406	$   24,601	$   26,609
Platinum	$   14,192	$   13,592	$   17,783	$   17,545
Bronze	$     1,150	$     2,146	$     1,426	$     1,114
Plastic	$           25	$         414	$     1,062	$         993
Total	$   39,489	$   40,419	$   48,611	$   50,165

YoY Growth	2019	2020	2021	2022
Paper	1.18	0.63	0.28	0.01
Gold	0.54	(0.18)	0.34	0.10
Silver	0.26	(0.01)	0.15	0.08
Platinum	0.27	(0.04)	0.31	(0.01)
Bronze	(0.08)	0.87	(0.34)	(0.22)
Plastic	(0.91)	15.43	1.57	(0.06)

normalization variables	2019	2020	2021	2022
A (Min of YoY Growth)	(0.91)	(0.18)	(0.34)	(0.22)
B (Max of YoY Growth)	1.18	15.43	1.57	0.10
a = 0	0	0	0	0
b = 1	1	1	1	1

Below is the normalized growth calcutated  ([YoY Growth]-A)*(b-a)/(B-A)

Normalized Growth	2019	2020	2021	2022
Paper	1.000	0.052	0.326	0.710
Gold	0.69	-	0.36	1.00
Silver	0.56	0.01	0.26	0.94
Platinum	0.56	0.01	0.34	0.64
Bronze	0.40	0.07	-	-
Plastic	-	1.00	1.00	0.48

This is what the chart in power bi would look like using just standard growth:

For purposes of this example, let's say that Paper, Gold, Silver...,etc is the Product Line. So at the product line level, the chart below is what the chart would look like for normalized growth in Power BI.

While the above example uses product line, I have to show the same for Product Category, Product Type, etc. which (I believe) means that I would have to have different measures for each dimension of the product table, or perhaps use a switch statement that cycles through each dimension.

Any thoughts and/or direction on this would greatly be appreciated.

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523