Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper III
Helper III

SelectedValue Issue???

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)




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)






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)
Location150125 200200
-- Project 15075 7575
-- Project 2100100 125125
Instead it looks something like this for Incremental revenue
Location00 00
-- Project 15075 7575
-- Project 2100100 125125
Not sure if this is relevant - but here is the calculated table formula @DataInsights put together for me, and it works GREAT (thanks!)
AllocatedProjectRevenue =
'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 =
TRUE (),
[Date] >= vAllocationStartDateYear1
&& [Date] <= vAllocationEndDateYear1, vRevenueYear1 / 12,
[Date] >= vAllocationStartDateYear2
&& [Date] <= vAllocationEndDateYear2, vRevenueYear2 / 12,
[Date] >= vAllocationStartDateYear3
&& [Date] <= vAllocationEndDateYear3, vRevenueYear3 / 12



Super User
Super User

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 %. 

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors