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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ENGFAKAYODE
Helper I
Helper I

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



1 ACCEPTED SOLUTION

Hi again, 

You need to creat a measure not a calculated column: 

 

onurbmiguel__0-1660750495237.png

 

 

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

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


View solution in original post

6 REPLIES 6
onurbmiguel_
Super User
Super User

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

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


I just tried it {8D77787B-5E53-4965-A792-325979A8C362}.png.jpg

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

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


{85DEACDC-3F49-45B3-9BC1-2467E4352121}.png.jpg

The columns are blank

Hi again, 

You need to creat a measure not a calculated column: 

 

onurbmiguel__0-1660750495237.png

 

 

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

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


Thanks

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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