Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
B_Smith
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
Anonymous
Not applicable

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
    )

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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
    )

Thanks for this! I think that is working!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.