DAX Workout 007 - Determine total sales of products based on certain words

[tweinzapfel]

Hi everyone,

As this is being posted on Easter Sunday, it makes sense to have an Easter theme. In this workout, you are provided a simple dataset of various products and their sales over time. And it is divided in two parts:

1. The first part is to create a measure that shows the total sales for all products with the word “Easter” in them. So this would include products such as “Easter Basket”, “Easter Egg Hunt Kit”, but also “Bonnet Hat, Easter”, etc.

2. The second part is similar. However, there are 4 product key words in the second tab of the data file and you are to create a single measure that will provide total sales for these.

An Excel data file is provided. The first tab has the sales information. The second tab is the list of product key words to be used for the second part.

Some important notes for this workout:

1. Only DAX measures should be used here. While it’s possible to create groups, use Power Query to filter, etc; the goal of this workout is to do this via DAX measures.
2. Assume that the product key words (2nd tab) are subject to change and so you want to create a measure that will accommodate this (i.e. no hard coding the key words in the measure)

POST Update: Image below was updated to remove the total of the bottom right table. See discussion in comments below.

The data file can be found here:
Workout 007 Data.xlsx (14.7 KB)

Submission
Load the supplied data file, create your solution, and reply to this post. Upload a screenshot of your solution (or the PBI file) along with any DAX items. Please blur any solution or place it in a hidden section.

[BrianJ]

@tweinzapfel ,

Happy Easter! Here’s my solution (as always, 100% gluten-, TI- and CALCULATE-free…):

NOTE: Excellent catch by @AlexisOlson about the double counting in the original totals. I have modified my Total Sales of Items measure to remedy that issue.

Click for DAX Code

Easter Product Sales = 

VAR __vTable_ = 
FILTER(
    ALLSELECTED( Data ),
    CONTAINSSTRING( Data[Product Name], "Easter" )
)

VAR __Result = 
SUMX(
    __vTable_, 
    [Total Sales]
)

RETURN __Result


Total Sales of Items = 

VAR __vTable_ =
FILTER(
    CROSSJOIN(
        DISTINCT( Data[Product Name] ),
        DISTINCT( 'Product Key Words'[Product Key Word])
    ),
    CONTAINSSTRING( [Product Name], [Product Key Word] ) = TRUE
)

VAR __DistinctCJoin =
    DISTINCT(
        SELECTCOLUMNS( __vTable_, "@Products", 'Data'[Product Name] )
    )
    
VAR __Result =
SUMX(
    __DistinctCJoin,
    [Total Sales]
)

RETURN __Result

DAX Workout 007 - Brian Julius Solution.pbix (9.2 MB)

[AlexisOlson]

The first is trivial with CALCULATE.

Easter Product Sales

CALCULATE (
    [Total Sales],
    CONTAINSSTRING ( Sales[Product Name], "Easter" )
)

The second needs some specification on what the “correct” subtotal should be (the one in the OP doesn’t seem right to me). My interpretation is that it should match the total shown on the left here:

Given this assumption, here’s my measure:

Key Word Sales

VAR _KeywordProducts_ =
    FILTER (
        VALUES ( Sales[Product Name] ),
        NOT ISEMPTY (
            FILTER (
                Keywords,
                CONTAINSSTRING (
                    Sales[Product Name],
                    Keywords[Product Key Word]
                )
            )
        )
    )
VAR _Result = CALCULATE ( [Total Sales], _KeywordProducts_ )
RETURN
    _Result

DAX 007 AlexisOlson.pbix (24.0 KB)

[tweinzapfel]

@AlexisOlson - you are absolutely correct and that was a great catch. And I would also agree with your assumption. $8k is the total amount of those items that have those keywords.

This presents an interesting visual dilemma as some of the keywords result in some products being counted more than once. So the issue is presenting this in a visual perspective as one may read the bottom total of the table to represent the sum of all the items above (which would be incorrect).

So one possible approach would be to make it clear on the totals such as this example:

For purposes of my posting above, I removed the total but referenced the discussion here. Would be great to see how others would address. And this issue required me have to go back to my original solution to revise it to reflect the correct total amount. My original one worked just fine at the individual product level, but from a total (which now has to account for all) didn’t work.

[Anurag]

Hi ,

My submissions for this work out:

Dax Code

KeyWordSales = 
VAR tb= SUMMARIZE(Data,Data[Product Name],Data[Price],Data[Quantity])
VAR tb3= ADDCOLUMNS(tb,"@Flag",VAR _cur=[Product Name]
                                VAr _sum=SUMX('Product Key Words',if(CONTAINSSTRING(_cur,[Product Key Word]),1,0))
                                return
                                _sum)
return
sumx(FILTER(tb3,[@Flag]<>0),[Price]*[Quantity])

Easter Product Sales = 
VAr _selkey="Easter"
VAr tb= sumx(FILTER(Data,CONTAINSSTRING(Data[Product Name],_selkey)),Data[Price]*Data[Quantity])
return
tb

Workout 007.pbix (53.6 KB)

Thanks

[KimC]

Hi Tim,

Thanks for the workout, please find my solution:

Summary

[Eze]

Thanks for the workout, pls find my submission below;

Easter Sales DAX Code

Summary

Easter Product Sales =
//Get total Sales for Product Names that contain “Easter”
CALCULATE(
[Total Sales],
FILTER(
Data,
CONTAINSSTRING(Data[Product Name],“Easter”)
)
)

Key Word Sales DAX Code. Thanks @AlexisOlson I learnt some tricks from your approach on this one.

Summary

Key Word Sales =

// Get Unique list of Products with Key Word Names
VAR _vtTableFilter =
FILTER (
VALUES ( Data[Product Name] ),
NOT ISEMPTY (
FILTER (
‘Product Key Words’,
CONTAINSSTRING (
Data[Product Name],
‘Product Key Words’[Product Key Word]
)
)
)
)

//Filter Total Sales for the matching Products in (_vtTableFilter)
VAR _Result =
CALCULATE(
[Total Sales],
_vtTableFilter
)

RETURN

_Result

[Hakeem]

This DAX workout got me pretty good. I was having problems finding ways to get the product name and product keywords into the same table until I ran across Brian’s solution.

I learned something new today. Gonna keep that trick in my back pocket!

Total Sales (Easter) = 

var easterTable = FILTER(Data, CONTAINSSTRING(Data[Product Name], "Easter")) -- Filter data table for rows where product name contains Easter

var result = SUMX(easterTable,[Total Sales]) -- get total sales from filtered table

return

result


Total Sales for Keywords = 

var joinedTable = FILTER(
                CROSSJOIN(VALUES(Data[Product Name]), VALUES('Product Key Words'[Product Key Word])), -- Table with cartesian product of
                CONTAINSSTRING(Data[Product Name],'Product Key Words'[Product Key Word]) =True          -- product name & product key word
)

var productColumn = DISTINCT(SELECTCOLUMNS(joinedTable,"@Product Name", Data[Product Name]))  -- distinct list of product names

var result = SUMX(productColumn, [Total Sales])  -- sum total sales of products

return

result

[donnclark]

April 13, 2023
Donn Clark (DEC)

DAX Here’s my entry for Workout 007. Fantastic way to learn directly from seeing how others set up solutions to Power BI Challenges.

Thanks

DAX007Easter - DEC.pbix (61.2 KB)

[tweinzapfel]

All - great approaches to this.

Here were my measures to this workout:

Approach

Total Sales = SUMX(Data, Data[Price] * Data[Quantity]) 

Total Sales (Easter Items Only) = 
VAR virTable = 
FILTER(
    Data,
    SEARCH("Easter", Data[Product Name], 1, -1) > 0
)
VAR Result = 
CALCULATE(
    [Total Sales],
    virTable    
)
RETURN
Result

Total Sales (Item Only)= 

VAR _Product = SELECTEDVALUE(Items[Product])
VAR virTable = 
    FILTER(
        ALL(Data),
        COUNTROWS(
            FILTER(
                Items,
                CONTAINSSTRING(Data[Product Name], Items[Product])
                )
        ) > 0

    )
VAR Result = 
    CALCULATE(
        [Total Sales],
        virTable
    )

RETURN
Result

Thanks again for all that provided solutions to this!

[MubarakBabs]

Here’s my solution for this workout. Calculating the Easter sales was pretty straightforward, but replicating the logic for the keywords in a table required a few more steps.

DAX code

Easter Product Sales = 
VAR easterproducts = 
    FILTER('Product Name', 
            FIND("Easter", 'Product Name'[Product Name],,0)>0 
            )
RETURN
CALCULATE([Total Sales], easterproducts)

Key Item Sales = 
VAR keyitem = VALUES('Product Key Words'[Product Key Word]) 
//generate a pairing of each keyword and product name to allow a row by row comparison
VAR allpairs = CROSSJOIN(keyitem, 'Product Name')
//check if the keyword exists in the product name on each row. Return 0 if it doesn't
VAR newtable = ADDCOLUMNS(allpairs, 
                "keyprod", FIND('Product Key Words'[Product Key Word], 'Product Name'[Product Name],,0))
//calculate total sales for each row where the keyword is present in the product name
VAR keyitemsales = CALCULATE([Total Sales], FILTER(newtable, [keyprod] > 0))
RETURN keyitemsales

[shahadathossen.shajj]

Here is my submission, for calculating keyword sales, I used two vTable, and if the context of key Word Column is Total, I summed up Total Sales which key Word wasn’t listed

Key Word Sales =
VAR _table = FILTER(
    all( Data ),
    SEARCH(
        SELECTEDVALUE( 'Product Key Words'[Product Key Word] ),
        Data[Product Name],,
        0
    ) > 0
) 
VAR _NewTable = ADDCOLUMNS(
    Data,
    "String Match", IF(
        SEARCH( "Easter", Data[Product Name],, 0 ) > 0,
        "Easter", IF(
            SEARCH( "Egg", Data[Product Name],, 0 ) > 0,
            "Egg", IF(
                SEARCH( "Spring", Data[Product Name],, 0 ) > 0,
                "Spring", IF(
                    SEARCH( "Chocolate", Data[Product Name],, 0 ) > 0,
                    "Chocolate"
                )
            )
        )
    )
) 

RETURN
IF(
    HASONEVALUE( 'Product Key Words'[Product Key Word] ),
    CALCULATE( [Total Sales], _table ),
    SUMX(
        FILTER( 'New Table','New Table'[String Match] <> "" ),
      'New Table'[Price] *'New Table'[Quantity]
    )
)

Sales Easter = 
CALCULATE(
    [Total Sales],
    FILTER(
        ADDCOLUMNS(
            Data,
            "String Match",
            IF(
            SEARCH(
                "Easter",
                Data[Product Name],
                ,
                0
            )>0,
            "Found",
            "Not Found"
        )
        ),
        [String Match] = "Found"
)
)

[mbraun]

Answer: