Calculating Contribution to Change for a dataset

[ItsPS]

Hi All,

I'm trying to calculate the Cont to Chage column in PowerBI Dax but struggling to do so :

We have to Sum of Diff column based on following logic:
1. When LEVEL 3, LEVEL 4 and LEVEL 5 are blank then check the differnece between Value TY and Value PY, it it is positive, than take the sum of all the values which are positive in Difference column where LEVEL 4 and LEVEL 5 are blank and show it in Base_Total coumn. To Calculate the Cont to Change, its always 100% when LEVEL 3, LEVEL 4 and LEVEL 5 are blank, rest it should be Difference divided by Base_Total. For Negative values of Difference column it shoud be blank

2. When LEVEL 3, LEVEL 4 and LEVEL 5 are blank then check the differnece between Value TY and Value PY, it it is negtaive, than take the sum of all the values which are negative in Difference column where LEVEL 4 and LEVEL 5 are blank and show it in Base_Total coumn. To Calculate the Cont to Change, its always 100% when LEVEL 3, LEVEL 4 and LEVEL 5 are blank, rest it should be Difference divided by Base_Total. For positive values of Difference column it shoud be blank

Note : There are multiple LEVELS in LEVEL 2 for each Store

Store	LEVEL 2	LEVEL 3	LEVEL 4	Value TY	Value PY	Difference	Base_total	Cont to Change
A	HS			129079714.6	125453294.7	3626419.9	8583514.55	100%
A	HS	1-2 M	1 M	21143243.36	18751140.9	2392102.46	8583514.55	28%
A	HS	1-2 M	2 M	39758681.06	34379155.16	5379525.9	8583514.55	63%
A	HS	1-2 M		60901924.42	53130296.06	7771628.36	8583514.55	91%
A	HS	3 M		30640785	29828898.81	811886.19	8583514.55	9%
A	HS	4+ M		37537026.85	42494099.8	-4957072.95	8583514.55
B	HS			119191481	126313240.1	-7121759.16	-7121726.6	100%
B	HS	1-2 M		52851435	53873788.1	-1022353.09	-7121726.6	14%
B	HS	1-2 M	1 M	21184857.12	18142429.72	3042427.41	-7121726.6
B	HS	1-2 M	2 M	31666567.03	35731358.38	-4064791.35	-7121726.6	100%
B	HS	3 M		30155189.8	32097831.04	-1942641.24	-7121726.6	27%
B	HS	4+ M		36184899.59	40341631.86	-4156732.27	-7121726.6	58%
B	AH			119191481	126313240.1	-7121759.16	-9249119.9	100%
B	AH	35 - 49		33923704.16	42592811.77	-8669107.61	-9249119.9	94%
B	AH	50+		78725040.38	76597649.21	2127391.17	-9249119.9
B	AH	50+	50-64	53384601.19	52509008.51	875592.684	-9249119.9
B	AH	50+	65+	25340439.18	24088651.55	1251787.63	-9249119.9
B	AH	Upto 34		6542776.591	7122788.925	-580012.334	-9249119.9	6%

[Elcin_7]

Hi, Question is not that clear there is no level 5 in the table. But I will explain how I would approch as far as I understood. I would create a conditional coloumn in power query and I would say if  level 2 and level 3 and level 4 is "null" tell "emthy" and else "data". And I would create a DAX formula depending this logic.

Difference TY PY =

IF(

    SELECTEDVALUE('Sheet1'[Custom]) = "Emthy",

    SUM('Sheet1'[Value TY]) - SUM('Sheet1'[Value PY]),

    0

)

With similar logic if the new calculated colum is "emthy" and you can show cont to change as %100 with If formula or else you can create your calculation.