Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there,
I've been trying to find a measure that can show the following results in a line and clustered chart:
3 responses in the year 2020 * € 100,- = € 300,-. This measure would be:
Saved costs = Calculate([Savings] * 100) -- after I created the measure 'Savings' to sum up all response dates in the table below.
But what if I want to select all years that have different costs per year, like:
3 responses in the year 2020 (€ 100,- per response) + 4 responses in the year 2021 (€ 118,- per response) + 2 responses in the year 2022 (€ 115,- per response) = € 1002,- in total.
Is there a way it can recognize the year in the response date, and make the right calculation if I would use a separate table with just the costs and year?
I hope somebody can help me. Thank you!
Year | Saved cost |
2020 | € 100 |
2021 | € 118 |
2022 | € 115 |
Office workers fill in the response date, so the measure shoud recognize how to make the right calculation.
Response date | Saved costs | |
6-6-2020 | € 100,00 | |
17-6-2020 | € 100,00 | |
20-12-2021 | € 118,00 | |
6-4-2021 | € 118,00 | |
27-3-2021 | € 118,00 | |
12-4-2021 | € 118,00 | |
11-4-2021 | € 118,00 | |
3-3-2022 | € 115,00 | |
28-2-2022 | € 115,00 |
@johnt75 Thank you for the response.
I can't get the one-to-many relationship as you might see in the screenshot below. I've tried so many different ways to get the correct results. It does show a total costs and a costs per year, but only 2022 is the correct total costs. Any idea how I could correct the model view? Thank you.
Can you remove the relationships between Tab3 and Tab1 and create a relationship from Tab3[Year] to Calendar[Year] ?
I've followed your last instruction, but it still showed incorrect results. According to a colleague it was caused by my relationship between Sent letters and Date, and he created some other measures. It seems to work, but I still have learn how exactly.
Anyways, thanks again for your help johnt75. 🙂
Link your Year Cost table to the date table in a one-to-many relationship and then you can create a measure like
Saved Costs = SUMX(
'Responses',
RELATED( 'Year Cost'[Saved cost])
)