Forum Discussion
SUM Values for specific Dates in different columns
Hi everyone,
I got a table with various data and have calculated the expected value for the coming years. Some values have been divided between cells due to assumptions within the table, hence "Y1" & "Y1 Value", "Y2" & "Y2 Value" etc. Please see the image attached. Next, I would like to create a line chart that shows the total sum for each year for the next 10 years - something like the line chart example attached but with Year 2022-2033.
I've managed to do this by grouping and merging through a new query, but this disrupt the relationship with the initial query as I'm not including the identity column (if I do I'm back to square one), thus the line chart won't react to any filtering within the desktop.
So my question is - is there a DAX formula I could use or a way around this within the query or the desktop to create a new measure that will calculate the total values depending on the various year columns? Still quite new to Power Bi and struggle to find a solution for this 😅 any help will be much appreciated! Example Line ChartYears & Value
5 Replies
Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi lbendlin - good idea.
I've created a sample data which can be found here - https://drive.google.com/drive/folders/1ttd6M2S2Cnxx4-QgxtTXOyS7VfcoNBou?usp=share_linkThe visual within the sample shows how I'd like to present it, but the issue I’m having is that it can't be filtered by product/region etc for all tables – Y1, Y2, Y3...
If I’m using the product & region column for Y1, it’ll only show the values within that table and not the total for all - if I'm using the initial table, non of them will be filtered..
However, before going too much into the above, the plan of my calculation has changed slightly, as I'd like to include the 10 Year Value into my calculation per year as well. Subsequently, for those values to be part of the total value per year within the visual.
The first assumptions here is like my initial calculations:- Our Y1 will be two years after Order Year (example: Order Year = 2023, then Y1 = 2025, Y2 = 2026, Y3 = 2027)
- Y1 = if [Typical Value] <= 250 then [Typical Value] else if [Typical Value] <= 750 then 250 else if [Typical Value] > 750 then 0 else 0
- Y2 = if [Typical Value]>250 and [Typical Value]<750 then ([Typical Value]-[Y1 Value]) else 0
- Y3 = if [Typical Value] > 750 then [Typical Value] else 0
However now I'd like to add the following assumption, but not sure how to do it:
If Typical Value = 100, 10 Year Value = 150, Y1 = 2025, then I'd like the remaining 10 Year Value (being 150-100=50), so 50, to be split between the following years up to 2033.
Meaning, 50/8 = 18.75, and as Y1 = 2025 I'd like 50 to be split between year 2026 to 2033 à 2026 = 18.75, 2027 = 18.75 etc for that specific row (and this to be done for all rows for the empty columns up to 2033).Example below, but here I'm showing the years in the columns which I know wouldn't work.
ID
Region
Project Name
Car Type
Total Quantity Required
Product
Typical Value
10 Year Multiple
10 Year Value
Y1
Y1 Value
2026
2027
2028
2029
2030
1
Asia
Alpha
Audi
3
B
100
0.5
150
01/01/2025
100
18.75
18.75
18.75
18.75
18.75
I'm still new to DAX so unsure on how I can find my way around this without having to create multiple tables & measures, which will then cause relationships to break, and I'd be unable to filter by product, region etc – as my initial problem.
Hope that makes sense - Thank you 🙂See attached for a version using a composite key.
Hi lbendlin - good idea.
The visual within the sample shows how I'd like to present it, but the issue I’m having is that it can't be filtered by product/region etc for all tables – Y1, Y2, Y3...
If I’m using the product & region column for Y1, it’ll only show the values within that table and not the total for all - if I'm using the initial table, non of them will be filtered..
However, before going too much into the above, the plan of my calculation has changed slightly, as I'd like to include the 10 Year Value into my calculation per year as well. Subsequently, for those values to be part of the total value per year within the visual.
The first assumptions here is like my initial calculations:- Our Y1 will be two years after Order Year (example: Order Year = 2023, then Y1 = 2025, Y2 = 2026, Y3 = 2027)
- Y1 = if [Typical Value] <= 250 then [Typical Value] else if [Typical Value] <= 750 then 250 else if [Typical Value] > 750 then 0 else 0
- Y2 = if [Typical Value]>250 and [Typical Value]<750 then ([Typical Value]-[Y1 Value]) else 0
- Y3 = if [Typical Value] > 750 then [Typical Value] else 0
However now I'd like to add the following assumption, but not sure how to do it:
If Typical Value = 100, 10 Year Value = 150, Y1 = 2025, then I'd like the remaining 10 Year Value (being 150-100=50), so 50, to be split between the following years up to 2033.
Meaning, 50/8 = 18.75, and as Y1 = 2025 I'd like 50 to be split between year 2026 to 2033 à 2026 = 18.75, 2027 = 18.75 etc for that specific row (and this to be done for all rows for the empty columns up to 2033).Example below, but here I'm showing the years in the columns which I know wouldn't work.
ID
Region
Project Name
Car Type
Total Quantity Required
Product
Typical Value
10 Year Multiple
10 Year Value
Y1
Y1 Value
2026
2027
2028
2029
2030
1
Asia
Alpha
Audi
3
B
100
0.5
150
01/01/2025
100
18.75
18.75
18.75
18.75
18.75
I'm still new to DAX so unsure on how I can find my way around this without having to create multiple tables & measures, which will then cause relationships to break, and I'd be unable to filter by product, region etc – as my initial problem.
Hope that makes sense - Thank you 🙂
