SelectedValue Issue???

[Anonymous]

I am trying to create a matrix that shows revenue by location and project .. The individual projects are working great, but the sum total on the matrix for the location is not summing the numbers... 

My formulas

Table: AllocatedProjectRevenue (calculated table, formula below)

Inputs:

Amount = Revenue for a project

Percent Incremental = % of incremental revenue

Percent Replacement = % of replacement revenue 

ID = Project ID #

Location = Production Location

Revenue Type = Table with three options for revenue type (Incremental, Replacement, Total)

Formulas:

Incremental Revenue = if(selectedvalue(AllocatedProjectRevenue[Percent Total]) = 1, SUMX(AllocatedProjectRevenue, AllocatedProjectRevenue[Amount])*SELECTEDVALUE(AllocatedProjectRevenue[Percent Incremental ], 0), 0)

 

Repleacement Revenue = if(selectedvalue(AllocatedProjectRevenue[Percent Total]) = 1, SUM(AllocatedProjectRevenue[Amount])*SELECTEDVALUE(AllocatedProjectRevenue[Percent Replacement ]), SUM(AllocatedProjectRevenue[Amount]))

*** want it to assume 100% replacement revenue unless incremental + replacement revenue = 100%; this section seems to be working fine

Revenue Type Dynamic = if(selectedvalue('Select Revenue Type'[Revenue Type]) = "Incremental", [Incremental Revenue], if(selectedvalue('Select Revenue Type'[Revenue Type]) = "Replacement", [Repleacement Revenue], [Incremental Revenue]+[Repleacement Revenue]))

The output should look something like this (which is what I'm getting for replacement revenue)

	2/2020	3/2020	...	6/2020	7/2020
Location	150	125		200	200
-- Project 1	50	75		75	75
-- Project 2	100	100		125	125

Instead it looks something like this for Incremental revenue

	2/2020	3/2020	...	6/2020	7/2020
Location	0	0		0	0
-- Project 1	50	75		75	75
-- Project 2	100	100		125	125

Not sure if this is relevant - but here is the calculated table formula @DataInsights put together for me, and it works GREAT (thanks!)

AllocatedProjectRevenue =
GENERATE (
'PP - Project Status: Revenue',
VAR vProjectID = 'PP - Project Status: Revenue'[Project IDId]
VAR vProjectEndDate = 'PP - Project Status: Revenue'[DP-F]
VAR vAllocationStartDateYear1 =
EOMONTH ( vProjectEndDate, 1 )
VAR vAllocationEndDateYear1 =
EOMONTH ( vAllocationStartDateYear1, 11 )
VAR vAllocationStartDateYear2 =
EOMONTH ( vProjectEndDate, 13 )
VAR vAllocationEndDateYear2 =
EOMONTH ( vAllocationStartDateYear1, 23 )
VAR vAllocationStartDateYear3 =
EOMONTH ( vProjectEndDate, 25 )
VAR vAllocationEndDateYear3 =
EOMONTH ( vAllocationStartDateYear1, 35 )
VAR vCalendar =
CALENDAR ( vAllocationStartDateYear1, vAllocationEndDateYear3 )
VAR vCalendarEOM =
FILTER ( vCalendar, [Date] = EOMONTH ( [Date], 0 ) )
VAR vRevenueYear1 = 'PP - Project Status: Revenue'[Revenue - Year 1]
VAR vRevenueYear2 = 'PP - Project Status: Revenue'[Revenue - Year 2]
VAR vRevenueYear3 = 'PP - Project Status: Revenue'[Revenue - Year 3]
VAR vResult =
ADDCOLUMNS (
vCalendarEOM,
"Amount",
SWITCH (
TRUE (),
[Date] >= vAllocationStartDateYear1
&& [Date] <= vAllocationEndDateYear1, vRevenueYear1 / 12,
[Date] >= vAllocationStartDateYear2
&& [Date] <= vAllocationEndDateYear2, vRevenueYear2 / 12,
[Date] >= vAllocationStartDateYear3
&& [Date] <= vAllocationEndDateYear3, vRevenueYear3 / 12
)
)
RETURN
vResult
)

[AllisonKennedy]

You may need to try MAX() or AVERAGE() instead of selected value. At the Project level when more than one location is selected, you have too many rows to look at and there is not just 1 selectedvalue for the %.