Starts with question

DAX DAX Calculations
1

I have a measure Total Sales = sum(SALES[Sales])

If the customer name starts with “Acme” reduce SALES[Sales] by 12%, if the customer name starts with “Vect”, reduce SALES[Sales] by 10%, if customer name starts with “Merc”, reduce SALES[Sales] by 9%, else sum(SALES[Sales]).

  1. Would it be more efficient to add a column on the SALES table called [Adjusted Sales] that figures this calculation, then create a new measure Total Adjusted Sales = sum(SALES[Adjusted Sales]), or just do this calculation with a measure?

  2. Whichever one is more efficient, how would I write it? The column would be in M language and the measure would be in DAX and I’m not sure how to write this formula in either.

Thanks for your help.

2

Hi @Usates,

In this case the order from Best to Worst is:

  1. Measure
  2. M custom Column
  3. DAX Calculated Column

But the only right choice is number 1, the Measure. That doesn’t need to be stored in your Model, will only get evaluated when used in your Report and more importantly it’s fully dynamic when it comes to the filter context thus allowing you several different insights with just one calculation.
A Custom column / Calculated column don’t share that capability, once calculated they are fixed.

There are a couple of ways to deal with your “Starts with” scenario but I like to use secondary table logic because that’s easy to maintain. Here’s my SalesAdjustment table:
image
I created that by going to “Enter Data” on the Home tab and manually typing it in but there are other ways to create a table like this of course.

You already have a [Total Sales] measure so I used measure branching to reference that:

Adjusted Sales = 
SUMX(
    SUMMARIZE( Sales, Sales[Warehouse Code], Dates[Date],
    "AdjSales", [Total Sales] * COALESCE( CALCULATE( VALUES( SalesAdjustment[Adjustment] ), FILTER( SalesAdjustment, SalesAdjustment[Name start] = LEFT( SELECTEDVALUE( Sales[Warehouse Code] ), 3) )), 1)),
    [AdjSales]
)

Couple of things happening here:

  1. SUMX we need an iterator to also calculate the Adjusted Sales for the Total
  2. SUMMARIZE creates the table to iterate over
  3. Next for each Sales[Warehouse Code] we’re going to FILTER the SalesAdjustment table to check if the first 3 characters are in the SalesAdjustment[Name start] column. If that is TRUE then it will return a single value from the SalesAdjustment[Adjustment] column. But if there is no match FILTER will return an empty table so in that case COALESCE will multiply by 1 otherwise the result would be a BLANK.

Here is the result.
image

I hope this is helpful. Here’s my sample file.
eDNA - Adjusted Sales.pbix (450.5 KB)

Alternatively you could also explore SWITCH TRUE logic.

1 Like
3

I am exploring the Switch True option.

Why does this not work?

sumx(SALES,
Switch( True(),
left((SALES[customer]),4) = “Acme” , SALES[Sales] *.88,
left((SALES[customer]),4) = “Vect” , SALES[Sales] *.90,
AND(left((SALES[customer]),4) = “Merc” , SALES[Ship Date] < 03/31/2020) , SALES[Sales] *.9,
SALES[Sales])
)

I want to only reduce “Merc” sales if they happened before 3/31/2020. Does the AND function work inside Switch?

4

I would replace the SALES[Sales] with your measure Total Sales

The AND is not needed

Guy

5

Hi @Usates,

Do you have a Date table set up in your model? Because you are referencing the fact table…
A clear issue is the equation on the date value please use DATE( 2020, 3, 31 )

You can use this measure below in the sample I provided earlier and check your logic.

Adjusted Sales v2 = 
SUMX(
    SUMMARIZE( Sales, Sales[Warehouse Code], Dates[Date],
    "AdjSales",
    SWITCH( TRUE(),
        LEFT( SELECTEDVALUE( Sales[Warehouse Code] ), 3) = "AXW", [Total Sales] * 1,12,
        LEFT( SELECTEDVALUE( Sales[Warehouse Code] ), 3) = "FLR", [Total Sales] * 1,1,
        LEFT( SELECTEDVALUE( Sales[Warehouse Code] ), 3) = "GUT" && Dates[Date] < DATE( 2019, 6, 1 ), [Total Sales] * 1,09,
        [Total Sales] )
    ), [AdjSales]
)
1 Like
6

Thank you!

7

@Melissa

Sorry I didn’t mean to interrupt what you were doing.

Apologize

Guy

8

Hi @GuyJohnson,

The forum is here for everyone to ask questions and to get involved. It’s a great way to practice what you’ve learned, expand your skills and at the same time help another member.

So when you have something to share please always do… :+1:

1 Like