cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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)

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 =
vCalendarEOM,
"Amount",
SWITCH (
TRUE (),
[Date] >= vAllocationStartDateYear1
&& [Date] <= vAllocationEndDateYear1, vRevenueYear1 / 12,
[Date] >= vAllocationStartDateYear2
&& [Date] <= vAllocationEndDateYear2, vRevenueYear2 / 12,
[Date] >= vAllocationStartDateYear3
&& [Date] <= vAllocationEndDateYear3, vRevenueYear3 / 12
)
)
RETURN
vResult
)

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

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.

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