Using ALL and grouping by month - compare 'filtered' sales to ALL sales per month.

[N_R_000]

Hi, 

Lets say I have 1 table with Sales/product data in. 

I already havea simple SUM(SalesValue) measure which I've used in a bar graph to show me the total sales per month. However, the report has various filters; product category, colour etc. which obviously change the monthly totals accordin to what's filtered. 

What I'd like to do is create a measure which shows me what the UNFILTERED totals are PER MONTH, so a clustered column chart can display these side-by-side. 

I've tried using ALL, ALLEXCEPT and ALLSELECTED but each try just gave me 1 total value, the total of the table, even if I put this into a monthly chart, every month is the whole unfiltered total, not the monthly unfiltered total.

How can I get an 'ALL PER MONTH' figure? Also, I have a date slicer, I'd like the 'all per month' total re respect this date slicer, but not the product/colour ones if possible.  

[amitchandak · Accepted solution]

@N_R_000 ,

not very clear

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]), all(Sales) )

or

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])), all(Sales) )

You can also use cross filtered

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

[Fowmy]

@N_R_000 

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn