Moving measure result to another dimension in DAX

user_3153712 0
Viewed 40

I need create a measure that takes the sum of sales of certain products and then shows that sum on a row with a different product.

I can get the desired sum as follows: Sales of A:=CALCULATE([Sales], 'Product'[Product] = "Product A")

Now I need to show that amount where the product is "Product B" (and the Customer is the same).

Product Customer Sales Sales of A Moved Sales
Product A ABC 10 10
Product A DEF 20 20
Product B ABC 30 10 10
Product B DEF 40 20 20
Product C ABC 50 10

A simple Moved Sales:=CALCULATE([Sales of A], KEEPFILTERS('Product'[Product] = "Product B")) does not work because the filter is removed by the inner calculation.

dax ssas
2 Answers

I'd try something like this:

Moved Sales:=
    IF(
       SELECTEDVALUE('Product'[Product])="Product B"
       ,[Sales of A]
       ,BLANK()
    )
user_190423790

Hello You can define such a measure:

Moved Sales =
VAR SalesOfA =
    ADDCOLUMNS (
        TblProduct,
        "Sales Of A",
            SWITCH (
                TblProduct[Product],
                "Product A", CALCULATE ( TblProduct[Sales], TblProduct[Product] = "Product A" ),
                "Product B", CALCULATE ( TblProduct[Sales], TblProduct[Product] = "Product B" ),
                CALCULATE ( TblProduct[Sales], TblProduct[Product] = "Product C" )
            )
    )
VAR Change_B_With_A =
    ADDCOLUMNS (
        SalesOfA,
        "B_With_A",
            SWITCH (
                TblProduct[Product],
                "Product A", BLANK (),
                "Product B", "Product A",
                BLANK ()
            )
    )
VAR Moved_Sales =
    ADDCOLUMNS (
        Change_B_With_A,
        "Moved Sales",
            IF (
                AND ( [B_With_A] = "Product A", TblProduct[Customer] = "ABC" ),
                CALCULATE (
                    [Sales],
                    ALL ( TblProduct ),
                    TblProduct[Product] = "Product A",
                    TblProduct[Customer] = "ABC"
                ),
                IF (
                    AND ( [B_With_A] = "Product A", TblProduct[Customer] = "DEF" ),
                    CALCULATE (
                        [Sales],
                        ALL ( TblProduct ),
                        TblProduct[Product] = "Product A",
                        TblProduct[Customer] = "DEF"
                    )
                )
            )
    )
VAR Result =
    SUMX ( Moved_Sales, [Moved Sales] )
RETURN
    Result

Step By Step Screenshots:

Step_01

Step_02

enter image description here

And last step to define [Moved Sales] measure :

You can slice and dice it.

Moved Sales = 
...
...
...
VAR Result = SUMX(Moved_Sales, [Moved Sales])
RETURN
    Result

Step_04

user_194690880
Related