Skip to main content
cancel
Showing results for 
Search instead 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

Reply
enswitzer
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)

 

 

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/20203/2020...6/20207/2020
Location150125 200200
-- Project 15075 7575
-- Project 2100100 125125
 
 
Instead it looks something like this for Incremental revenue
 2/20203/2020...6/20207/2020
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 =
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
)
 
 
 
 

 

 

1 REPLY 1
AllisonKennedy
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.
www.excelwithallison.com

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.