Forum Discussion

ENGFAKAYODE's avatar
ENGFAKAYODE
Icon for Helper I rankHelper I
4 years ago
Solved

DAX measure comparison

Hello Everyone,
I've 2 tables ;

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             ,

Product Name (for reference)    - Sales

Bike                                               100

Helmet                                           10

Soccer Ball                                      5

Jacket                                             25         .

I'm trying to get 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

I used:

SubCategory Sales =
VAR CurrentSubCategory =
VALUES ( tab1[  Product Sub Category] )
RETURN
CALCULATE (
SUM ( tab2[Sales] ),
ALL ( tab1[Product Name   ] ),
tab1[  Product Sub Category] = CurrentSubcategory)
to get the subcategory sales   
AND
Category Sales =
VAR CurrentCategory =
VALUES ( tab1[ Product Category ] )
RETURN
CALCULATE (
SUM ( tab2[Sales] ),
ALL ( tab1[Product Name   ] ),
tab1[ Product Category ] = CurrentCategory
) to get category sales,
but both has been giving me the values for the product sales.
Thanks in Advance



Need Help
  • onurbmiguel_'s avatar
    onurbmiguel_
    4 years ago

    Hi again, 

    You need to creat a measure not a calculated column: 

     

     

     

    SubCategory Sales = 
var _subcategory = SELECTEDVALUE('Table'[Prod Sub Category])

var _value = 
CALCULATE(
    sum('Table'[tab2.sales]),
    filter (ALL('Table'), 'Table'[Prod Sub Category]= _subcategory)
)
return 
_value

     

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
    Best Regards
    BC

6 Replies

  • onurbmiguel_'s avatar
    onurbmiguel_
    Icon for Power Participant rankPower Participant
    4 years ago

    Hello ENGFAKAYODE

    Try this solution: 

     

    SubCategory Sales =
    VAR CurrentSubCategory =
    VALUES ( tab1[  Product Sub Category] )
    RETURN
    CALCULATE (
    SUM ( tab2[Sales] ),
    filter (ALL ( tab1) , tab1[  Product Sub Category] = CurrentSubcategory)
    )
     
    to get the subcategory sales   
    AND
     
    Category Sales =
    VAR CurrentCategory =
    VALUES ( tab1[ Product Category ] )
    RETURN
    CALCULATE (
    SUM ( tab2[Sales] ),
    filter ( ALL ( tab1) , tab1[ Product Category ] = CurrentCategory )
    )
     
    i usually use
    filter( all ( table_XYZ) , "filter_1" && "filter_2"  ) 
    to catch everything and then i choose what i want to keep.
     
    Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
    Best Regards
    BC
      • onurbmiguel_'s avatar
        onurbmiguel_
        Icon for Power Participant rankPower Participant
        4 years ago

        hi again 

         

        change in line 8 :

         

        ... product sub category]  in CurrentSubcategory

         

        you are using the function "values" in the fisrt var so you will have muliples values. 

         

        another way is to change Values to Selectedvalue 

         

        try please 

         

        Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! ;-
        Best Regards
        BC