DAX measure comparison

[ENGFAKAYODE]

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

[onurbmiguel_]

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

[ENGFAKAYODE]

I just tried it 

[onurbmiguel_]

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

[ENGFAKAYODE]

The columns are blank

[onurbmiguel_ · Accepted solution]

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

[ENGFAKAYODE]

Thanks