determining percent range from two column totals.

[MrLube]

I have three product streams that I am spending on (Actuals column)

I have a column that indicates the Plan of spending (Plan column)

New data is entered after the previsous months bills are tallied for each stream.

I need to determine if the "Actual" spending total is within a 10% (+/-5%) of plan.

I am looking for a result of 1 or 0

My totals for each have been derived from a measure using VAR.

PowerBI is not allowing me to create a measure that includes SUM when it is not a column, therefore I cannot use the below expression.

SAMPLE DATA:  using equation =IF(AND(SUM(D22/C22)>=0.95,SUM(D22/C22)<=1.05),"1","0")

	A	B	C	D	E	F
1	Month	Product Stream	Plan	Actuals
2	2/1/2022	Water Based	$   14,619,362.99	$     6,424,296.78
3	2/1/2022	Air Based	$   14,249,594.37	$   15,855,890.04
4	2/1/2022	Solar Based	$   13,338,123.99	$     5,896,612.58
5	3/1/2022	Water Based	$   15,993,096.00	$   14,591,524.23
6	3/1/2022	Air Based	$   13,088,130.22	$   13,363,313.64
7	3/1/2022	Solar Based	$     9,948,330.84	$   16,668,038.00
8	9/1/2022	Solar Based	$   16,649,343.56	$   19,476,394.30
9	10/1/2022	Water Based	$     6,013,898.25	$     6,150,882.42
10	10/1/2022	Air Based	$     9,114,130.67	$   14,770,344.48
11	10/1/2022	Solar Based	$   13,856,225.44	$     4,541,430.10
12	11/1/2022	Water Based	$     4,623,342.56	$     8,355,904.00
13	11/1/2022	Air Based	$   10,585,451.49	$     9,948,330.84
14	11/1/2022	Solar Based	$   14,770,344.48	$   16,649,343.56
15	12/1/2022	Water Based	$     4,541,430.10	$     5,896,612.58
16	12/1/2022	Air Based	$     8,355,904.00	$     6,150,882.42
17	12/1/2022	Solar Based	$   14,834,497.47	$   13,338,123.99
18	1/1/2023	Water Based		$                         -
19	1/1/2023	Air Based		$                         -
20	1/1/2023	Solar Based		$                         -
21						outcome
22			$ 184,581,206.43	$ 178,077,923.96		1

the goal is to have a score card look similar to this:

the outcome of a 1 or 0 will allow me to create a pie chart with a measure depicting green or red based on 1 or 0.

Any help with this score card and expression or percentage range would be great.

[bharath_v · Accepted solution]

To determine if the "Actual" spending total is within a 10% (+/-5%) of plan, you can follow these steps:

1. Create a new column to calculate the percentage difference between the actual spending and plan spending for each product stream using the following DAX formula:

   % Difference = ABS('Table'[Actuals] - 'Table'[Plan]) / 'Table'[Plan]

   This formula calculates the absolute percentage difference between the actual and plan spending for each product stream.

2. Create a new column to check if the % difference is within the 10% (+/-5%) range using the following DAX formula:

   Within Range = IF( 'Table'[% Difference] <= 0.1 && 'Table'[% Difference] >= 0.05, "1", "0" )
   
   

   This formula checks returns 1 (Within Range) if it is, or 0 (Outside Range) if it isn't.

Hope this helps.

If I answered your question then pls mark my post as "Accept it as the solution" and a kudo would be appreciated.