Power BI - DAX measure. Slicer values from another table

0
votes

Three tables. Many to one relationship between A - X, and B - X (cross filter direction both selected in Power BI)

As screenshot below displays, in PBI I'd like to have a measure called Return %. My date slicer works fine (TableX[Date]), it returns per day sales amount and untis sold/returns.

However, the second slicer (TableA[Product]) only returns accurately Sales. In this instance:

100+120 = 220

Which DAX measure would enable the second slicer to filter correctly through table B? I've tried with:

return% = calculate(sum(B[Returns]) / sum(B[Units sold]), SELECTEDVALUE(A,[Product])

Without any results..Please find below Screenshot for illustration.

Thanks

enter image description here

1
powerbidaxslicers
you got 11% result? here what is the problem? – Siva
Hi Siva, The calculation is just 1/9 = 11% (for illustration purposes). I'm trying to replicate this with two slicers, with values from table A and X, in Power BI. Thanks – Adni
can you attach data sheet file? – Siva

1 Answers

3
votes

You are probably looking for something like this:

return% =
VAR selection_A_product = SELECTEDVALUE ( A[Product] )
RETURN
    CALCULATE (
        SUM ( B[Returns] ) / SUM ( B[Units sold] ),
        B[Product] = selection_A_product
    )