Issue with Aggregating Measures Using SELECTEDVALUE()

[VT_KMS]

Hi all,

I am having an issue with using SELECTEDVALUE(). I created a measure with SELECTEDVALUE() to calculate revenue change at the product level.

However, when I try to aggregate that value to the month level, it doesn't work if PRODUCTKEY is not present in the visual.

Expected value should be -15,828.

Here are my dax measure:

revenue_change_product =
var __category = SELECTEDVALUE(ARRDATA_CUSTOMER_PRODUCT[CATEGORY_PRODUCT])
var __curAmount = SUM(ARRDATA_CUSTOMER_PRODUCT[ARR_AMOUNT])

RETURN
SWITCH(
TRUE(),
__category = "CROSS-SELLPRODUCT1", __curAmount - [PRE_CROSS-SELL1],
__category = "LOST-PRODUCTPRODUCT1", __curAmount - [PRE_LOST_PRODUCT1],
__category = "PRODUCT REACTIVATIONPRODUCT1", __curAmount - [PRE_REACT_PRODUCT1]
)

sum_updownsell_product =

SUMX(

    FILTER(

        (customer_product_measure),

        SELECTEDVALUE(customer_product_measure[MONTH])

    )

    ,

    [revenue_change_product]

)

How can I properly aggregate this measure to the month level even when PRODUCTKEY is not in the visual? Any suggestions would be greatly appreciated!

Thank you!

[v-hashadapu]

Hi @VT_KMS , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @VT_KMS , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @VT_KMS , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[sjoerdvn]

You could try something like below.

revenue_change_product =
CALCULATE(SUM(ARRDATA_CUSTOMER_PRODUCT[ARR_AMOUNT]) - [PRE_CROSS-SELL1], KEEPFILTERS(ARRDATA_CUSTOMER_PRODUCT[CATEGORY_PRODUCT] = "CROSS-SELLPRODUCT1")
+ CALCULATE(SUM(ARRDATA_CUSTOMER_PRODUCT[ARR_AMOUNT]) - [PRE_LOST_PRODUCT1], KEEPFILTERS(ARRDATA_CUSTOMER_PRODUCT[CATEGORY_PRODUCT] = "LOST-PRODUCTPRODUCT1")
+ CALCULATE(SUM(ARRDATA_CUSTOMER_PRODUCT[ARR_AMOUNT]) - [PRE_REACT_PRODUCT1], KEEPFILTERS(ARRDATA_CUSTOMER_PRODUCT[CATEGORY_PRODUCT] = "PRODUCT REACTIVATIONPRODUCT1")

[v-hashadapu]

Hi @VT_KMS , Thank you for reaching out to the Microsoft Community Forum.

We have two methods to do this:
By using CALCULATE, you modify the filter context to ignore the product-level details while keeping the month-level context intact. SUMX then iterates over all products and sums the revenue changes correctly. This is the most efficient and robust solution, especially for larger datasets.

sum_updownsell_product =

CALCULATE(

    SUMX(

        ALL(ARRDATA_CUSTOMER_PRODUCT),  -- Removes product-level filters

        [revenue_change_product]

    ),

    VALUES(ARRDATA_CUSTOMER_PRODUCT[MONTH]) -- Keeps the month-level filter

)

The second approach uses SUMMARIZE to create a temporary table grouped by month, then uses SUMX to iterate over it and aggregate the revenue changes. While this works perfectly fine, it may be less performant on large datasets due to the table creation step.

sum_updownsell_product =

SUMX(

    SUMMARIZE(

        ARRDATA_CUSTOMER_PRODUCT,

        ARRDATA_CUSTOMER_PRODUCT[MONTH],

        "MonthlyRevenueChange", SUM([revenue_change_product])

    ),

    [MonthlyRevenueChange]

)

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...