cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
B_Smith Frequent Visitor
Frequent Visitor

DAX Comparison Measure

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHaas Established Member
Established Member

Re: DAX Comparison Measure

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
    )
3 REPLIES 3
ChrisHaas Established Member
Established Member

Re: DAX Comparison Measure

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 Frequent Visitor
Frequent Visitor

Re: DAX Comparison Measure

Thanks for this! I think that is working!

Super User
Super User

Re: DAX Comparison Measure

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.