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.
Hi Guys,
This is what I want to do, create another column or measure that calculates Total sales per month per project. My data is exactly how it is in the table without the "Total Sales" column i'm asking help for.
Thanks
Project | Date | Sales | Total Sales |
A | 01/01/2018 | 10 | 10 |
A | 01/02/2018 | 20 | 30 |
A | 01/03/2018 | 30 | 60 |
A | 01/04/2018 | 40 | 100 |
A | 01/05/2018 | 50 | 150 |
B | 01/01/2018 | 5 | 5 |
B | 01/02/2018 | 10 | 15 |
B | 01/03/2018 | 15 | 30 |
B | 01/04/2018 | 20 | 50 |
B | 01/05/2018 | 30 | 80 |
Solved! Go to Solution.
@KH11NDR,
Please check attached PBIX file.
Regards,
Lydia
What's the Dax for this as a measure or column as I can't get the Quick measure calculation to do what I want.
@KH11NDR,
Create a measure using DAX below.
total sales = CALCULATE(SUM(Table[Sales]),FILTER(ALL(Table[Date]),Table[Date]<=MAX(Table[Date])))
Regards,
Lydia
Sorry, That hasn't worked for me.
@KH11NDR,
What error message or what result do you get? Do you create a measure? Make sure you create a measure rather than a calculated column.
Regards,
Lydia
Yes it's a measure, I don't get any error messages, just a blank row, column.
@KH11NDR,
Do you create table visual as mine? Could you please share PBIX file to me? You can share PBIX file through OneDrive.
Regards,
Lydia
Do you think it has anything to do with me Unpivoting my columns?
Original Data
Project ID | 01/07/2017 | 01/08/2017 | 01/09/2017 | 01/10/2017 | 01/11/2017 | 01/12/2017 | 01/01/2018 | 01/02/2018 |
AAAA | 0 | 5 | 10 | 15 | 20 | 25 | 30 | 35 |
BBBB | 0 | 7 | 12 | 18 | 24 | 30 | 36 | 42 |
Power BI Table
ProjectID | Dates | Value |
AAAA | 01/07/2017 | 0 |
AAAA | 01/08/2017 | 5 |
AAAA | 01/09/2017 | 10 |
AAAA | 01/10/2017 | 15 |
AAAA | 01/11/2017 | 20 |
BBBB | 01/07/2017 | 0 |
BBBB | 01/08/2017 | 7 |
BBBB | 01/09/2017 | 12 |
BBBB | 01/10/2017 | 18 |
BBBB | 01/11/2017 | 24 |
And then in Power BI Matrix table, another row showing the cumualtive totals.
@KH11NDR,
Please check attached PBIX file.
Regards,
Lydia
Thank you, I'll use that as my suggestion, I know where I was going wrong now, I was using my Master Calendar Months for the column and not the date field from my table.
Thanks again, I'm pretty sure I'll be needing more help, Im an X QlikView
HI @KH11NDR
Have you tried using the quick measures to try and get your result? Right click on your dataset and select 'Quick Measure'. Select your value and any other options and this may give you what you want.
Let us know how you get on.
Thanks
shebr
How would you do it? As it's not working for me.
I was just trying that,
I selected Running total calculation, Base value as Sales and Field as Project, I was hoping that would have worked, but It's giving the same figures in the month rather than do a runninig total as expected.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |