A conditional MAX?

[acs]

Hello - have been learning a lot by reading these forums and all the helpful posts on here. Now I've run up against a problem I cannot find the answer for and hoping someone can help.

I have data listing a number of products we offer. The columns are:

1. Product name (e.g. Red BMX, Blue BMX, Green Racer etc.)

2. Version number (v1, v2, v3 etc.)

3. Product Type (e.g. BMX, Racer, Chopper etc.)

4. Month

5. Number of Sales

Something like this:

Project	Version	Type	Month	Sales
Red BMX	1	BMX	Jan 2017	53
Blue BMX	1	BMX	Jan 2017	21
Green Racer	1	Racer	Jan 2017	981
Red BMX	1	BMX	Feb 2017	98
Red BMX	2	BMX	Feb 2017	103

The issue I have is when I set-up any charts to total sales by type it shows (as the function should) the total sales for all BMXs together. So for the above a MAX gives me 103 (the highest sales figure for anything with type=BMX)

However, I want sales of v2 of the Red BMX to be added to the sales of v1 - however a SUM function doesn't work for me either, as the monthly figures we get are already totalised so include previous months' sales.

I want to be able to see (and show in charts):

a) how many BMXs have we sold regardless of version or colour

b) how many Red BMXs (and how many Blue etc.)

c) how many Red BMXs by version number

Is there a way to do this that I am missing - that is scalable and repeatable (as we add further versions, colours etc.?).

Thanks in advance.

[v-caliao-msft · Accepted solution]

@acs,

Could you give us a expected chart as the sample? To get the vaule you want, we can use ALLEXPECT function.
TypeTotal = CALCULATE(SUM(Table1[Sales]),ALLEXCEPT(Table1,Table1[Type]))
ProjectTotal = CALCULATE(SUM(Table1[Sales]),ALLEXCEPT(Table1,Table1[Type],Table1[Project]))
VersionTotal = CALCULATE(SUM(Table1[Sales]),ALLEXCEPT(Table1,Table1[Type],Table1[Project],Table1[Version]))

Regards,

Charlie Liao

[Greg_Deckler]

I may not totally understand what you are going for, but if I understand correctly, couldn't you do something like:

TotalSales = CALCULATE(SUM([Sales]),FILTER(BMX,[Month]=MAX([Month])))

[MarcelBeug]

My suggestion would be to normalize your source data, either at the real source or in Power Query:

1. Put the colour in a separate column.
2. De-accumulate Sales figures.

In your example: would the 103 Version 2 Sales include the 98 Version 1 Sales?

Does accumulation start each year in January?

What is your format for Month: text or a date (e.g. first of the month)? This require to de-accumulate the Sales correctly.

[acs]

 Appreciate the swift replies. Going to try and work my way through both suggestions and see if I can get either to work.

Meanwhile, if it helps:

"In your example: would the 103 Version 2 Sales include the 98 Version 1 Sales?"

No.

"Does accumulation start each year in January?"

Yes (well, not January but at the beginning of our financial year, which is August - so totals run up to 31st July, then reset).