Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
@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
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])))
My suggestion would be to normalize your source data, either at the real source or in Power Query:
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.
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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |