I have total sales amount but I need a column where I get total of sales per city and per month using dax function

Question

I have a data showing total sales. Can I get new column where I can see the total of sales per city using dax function.

This question is related to the following Learning Module

Answer 1

Hi Vishal Shendge,

Thank you for reaching out to us on the Microsoft Q&A forum.

I have created the necessary DAX expressions to calculate the total sales per city and per month. Here's how it's done:

Total Sales per City: Using the DAX formula, we calculate the total sales for each city by removing any filters except for the city column. This gives the total sales specific to each city.

Formula for Total Sales per City:

Total Sales per City =

CALCULATE(SUM([Sales]),

ALLEXCEPT('Table Name', 'Table Name'[City Column])

)

Total Sales per Month: Similarly, to calculate the total sales for each month, I have used a DAX expression that removes all filters except for the month column, allowing us to get the sales totals for each month.

Formula for Total Sales per Month:

Total Sales per Month =

CALCULATE(SUM([Sales]),

ALLEXCEPT('Table Name', 'Table Name'[Month Column])

)

This approach ensures that you can view the total sales dynamically based on either the city or month, and it updates based on the filters you apply in your reports.

If you are still facing any issue, please let us know in the comments and where you are encountering this error along with the screenshot for reference. We are glad to help you.

If you found the information helpful, we would greatly appreciate it if you could acknowledge it by selecting the Accept Answer & Upvote options.

Thank you.