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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DM_95
Frequent Visitor

Calculate Sales by Delivery Country

Hi,

 

I want to calculate the Sales by Customer by Delivery Country. For example, when a random Customer is selected in the slicer, the measure needs to split Sales by Delivery Country (for example, Sales in Germany and in Spain). When there is only 1 Delivery Country, it needs to show Total Sales.

 

Field "Delivery Country" is in another table (called Address) than field "CustomerName" (in table "Customer")

 

Can anyone help me out with the measure?

 

Thanks!

1 ACCEPTED SOLUTION

Yep, so it's the same formula you are using currently with your measures, but put a "Calculate" function around them. Should look something like this:

PY_USA = Calculate(your existing previous year formula, filter('Countries', 'Countries'[name]="USA"))

 

Same with the Previous year -1.

 

So, what happens is if there are no locations of a customer within the country, your total for this measure should result in 0

View solution in original post

7 REPLIES 7
DM_95
Frequent Visitor

@Keith-Sayer OK, thanks for the information. Would you happen to know if 2 new measures for Net Sales PY (Net Sales last year) and Net Sales PY-1 (Net Sales 2 years ago) which split by Delivery Country first, will eventually work?

 

Thanks!

Yes, that could work, too, but keep in mind that on the graph it will still make multiple lines if you are still splitting by that column on the x column, I think it's called width.

 

If it were me, I would keep those measures as they are on the chart and then make a few card visuals for each country (assuming you only have a certain number) OR a table visual if you have a lot of them.

@Keith-Sayer Thanks for your advice 🙂 I do want to try out the option with the measures, just to see how it looks like. Do you have an idea what the DAX-formula for this would be?

Yep, so it's the same formula you are using currently with your measures, but put a "Calculate" function around them. Should look something like this:

PY_USA = Calculate(your existing previous year formula, filter('Countries', 'Countries'[name]="USA"))

 

Same with the Previous year -1.

 

So, what happens is if there are no locations of a customer within the country, your total for this measure should result in 0

Keith-Sayer
Resolver I
Resolver I

There are a couple ways you can do this depending on how your data is structured.

 

It sounds to me like you have three tables in play:

-Customers

-Addresses
-Sales

 

  1. Do you have a key column between the "sale" table and the "address" table? If so, connect them with a one to many relationship (one being the address, many being the sales).
  2. Now you need to do the same thing between "sales" and "customers." a Many (sales) to 1 (customer) relationship.
  3. So now you have a diagram that should look kind of like this:
    KeithSayer_0-1667400101305.png

     

    Now here's what I would recommend to be easiest and avoid making too many measures. Just do a simple measure like 

    Total Sales = sum('Sales'[Amount])

    And then throw that on a visual with the field "Country" on an axis or the legend (depending on what type of diagram you use)

    Then add your "Customer" Slicer elsewhere on the page.

The reason this works is you have 2 1-n relationships affecting the sales table, one using customers and the other using addresses. They can both be used as filters, thus the total you get should be what you are looking for.

 

In the case you mentioned where a customer only has one country, that's okay, it will still show the total, it will just note what country that was.

@Keith-Sayer Yes, I do have a structure as you mentioned. However, when I want to display this in a line and clustered column chart (see screenshot below), it splits Net Sales correctly (blue column for Belgium and yellow for Germany) but not Net Sales PY (Net Sales last year) and Net Sales PY-1 (Net Sales 2 years ago). Is there any solution to this? This is why I would opt to write a new measure to split this.

 

Thanks!

 

DM_95_0-1667400966226.png

 

Oh I see, you need that line to be split by color as well?

 

I don't think that's possible as a line (you would really need multiple lines to visualize this type of data in line chart). You could definitely put your previous year and previous year -1 measures as additional columns and then it should split them out appropriately.

 

The other option is to set Country as the "x-axis" and then put what you have currently in the x-axis as a legend, that should work. The caveat being, previous year and prior year measures won't split by this factor.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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