measure question

[NM2023]

I have 2 fact tables - sales and opportunity. I want to show by opptunity ID, the sales revenue, and sales rev total. 

I have a measure, however this measure does not give me the total. 

M_TotalRev_PertainingToOpty =

SUMX(

    VALUES(Opportunity[ID]),

    VAR Acct = SELECTEDVALUE(Opportunity[AcctNum])

    VAR OptyProd = SELECTEDVALUE(Opportunity[Prod])

    var result = CALCULATE([M_totalRev],Sales[AcctNum]=Acct, Sales[Prod] = prod)

return result)

Unless, I have to make another measure 

M_TotalRev_PertainingToOpty_Agg =

SUMX(

    VALUES(Opportunity[ID]),

    [M_TotalRev_PertainingToOpty])

Why, and how can I modify the first measure to display result at both line and total level?

Thanks!
NM

[Anonymous]

Hi @NM2023 ,
Based on your description and the dax expression provided, the metrics not displaying totals as expected may be due to the way the function is used in conjunction with the function. The function creates a context that is limited to the current row (opportunity ID in this case) and your measure will correctly calculate the revenue for each opportunity ID. However, for totals, Power BI tries to evaluate your measures in a different context, in which case the row context provided by Power BI is not directly applicable. To modify your first measure so that it displays results at both the row and total levels, you might consider adjusting the method used to ensure that the entire dataset is correctly summarized when calculating totals.
Revised version:

M_TotalRev_PertainingToOpty = 
SUMX(
    VALUES(Opportunity[ID]), 
    VAR Acct = SELECTEDVALUE(Opportunity[AcctNum])
    VAR OptyProd = SELECTEDVALUE(Opportunity[Prod])
    RETURN
        CALCULATE(
            [M_totalRev],
            FILTER(
                ALL(Sales),
                Sales[AcctNum] = Acct && Sales[Prod] = OptyProd
            )
        )
)

If the above expression does not achieve your result, please provide the pbix file and hide the sensitive information in advance so that we can better help you.

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[tharunkumarRTK]

In total, there wont be any filters on ID and selectedvalue() function can give blank as a output. Which means the variables Acct and OptyProd value is blank. I guess this is the reason for a blank value in the total.
Could you explain the reason for using selectedvalue function inside an SUMX iterator?