DAX Comparison Measure

[B_Smith]

Hello, sorry if there are multiples, was not appearing in the topic list!

I'm trying to get a Dax calculation right for a measure and having some problems. I know it's some combination of allselected(), all() or allexcept() probably but I'm struggling! 

Here is an example of the situation data:

Product Dimension Table:

Product Name     -      Product Category      -    Product Sub Category

Bike                                 Sporting Goods                          Cycling

Helmet                            Sporting Goods                          Cycling

Soccer Ball                      Sporting Goods                           Soccer

Jacket                             Apparel                                        Outerwear

Which would be related to the sales table:

Product Name (for reference)    - Sales

Bike                                               100

Helmet                                           10

Soccer Ball                                      5

Jacket                                             25

What I'm trying to get is the Category and Sub Category Sales as measures for comparison, so it would be like below:

Product Name              -           Product Sales          -           Sub Category Sales       - Category Sales

Bike                                               100                                        110                                 115

Helmet                                           10                                         110                                 115

Soccer Ball                                       5                                            5                                   115

Jacket                                             25                                           25                                   25

What I've been trying is:

Sub Category Sales=Calculate(sum(Sales[sales]),allselected('product'[sub category]) or allexcept() in place of allselected

Category Sales=Calculate(sum(Sales[sales]),allselected('product'[category]) or allexcept() in place of allselected

But it is only showing the product sales for all three, I'm sure I am close but just not understanding something with DAX filter context.

Thanks for any help!

[Ashish_Mathur]

Hi,

Why would you need something like this?

1. Create a relatioship from the Product Name column of the Sales table to the Product Name column of the Product Dimension Table.
2. Drag the three columns from the Product Dimension table
3. Write this calculated field formula

=SUM(Data[Sales])

This will create a visual with 2 columns.

[Anonymous · Accepted solution]

Try this:

[SubCategory Sales] =
VAR CurrentSubCategory =
    VALUES ( ProductTable[Product Subcategory] )
RETURN
    CALCULATE (
        SUM ( Sales[Sales] ),
        ALL ( ProductTable[Product Name] ),
        ProductTable[Product Subcategory] = CurrentSubcategory
    )

and this:

[Category Sales] =
VAR CurrentCategory =
    VALUES ( ProductTable[Product Category] )
RETURN
    CALCULATE (
        SUM ( Sales[Sales] ),
        ALL ( ProductTable[Product Name] ),
        ProductTable[Product Category] = CurrentCategory
    )

[B_Smith]

Thanks for this! I think that is working!