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 Power BI Community,
I have three related tables, as shown in the attached image below.
My objective is to graph [Conditional Sum of Revenue vs. Years]
The conditions that I am looking for are:
Resulting Table should look like..
2021 | $ 7,050 |
2022 | $ 8,314 |
2023 | $ 9,721 |
2024 | $ 9,022 |
2025 | $ 7,248 |
Obviously, my data set is much bigger than just few rows that I'm showing here.
But I think the sample data is enough to communicate the principal.
Thank you so much,
Solved! Go to Solution.
You can put any logic to filter whatever, I just showed an example, for example, there are many choices to handle this. You can add new column in your table to identify what to include and exlude
Flag = IF( Table_Iterations[ID] >= 60004 && Table_Iterations[ID] <= 61004, 1, 0 )
and now update measure
Total Revenue = CALCULATE( SUM( Table_Time_Series[Revenue] ), Table_Iterations[Flag] = 1, Table_Group[Group__Name] IN { "Group_A", "Group_B" } )
or condition directly in the measure
Total Revenue = CALCULATE( SUM( Table_Time_Series[Revenue] ), Table_Iterations[ID] >= 60004, Table_Iterations[ID] <= 10004, Table_Group[Group__Name] IN { "Group_A", "Group_B" } )
I'm not fully understood 2nd part of your question.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@tk0501 please provide pbix with sample data, shared the link thru onedrive/google drive.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hello @parry2k ,
Thank you taking an interest.
Below link contains two files (pbix and Excel with original data).
One Drive Link to Sample pbix file and Excel with Sample Data
Thanks!
@tk0501 here is what you can use, change the columns and values based on your business logic
Total Revenue = CALCULATE( SUM( Table_Time_Series[Revenue] ), Table_Iterations[ID] IN {60004, 60005, 60006, 60007 }, Table_Group[Group__Name] IN { "Group_A", "Group_B" } )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks @parry2k ,
You can put any logic to filter whatever, I just showed an example, for example, there are many choices to handle this. You can add new column in your table to identify what to include and exlude
Flag = IF( Table_Iterations[ID] >= 60004 && Table_Iterations[ID] <= 61004, 1, 0 )
and now update measure
Total Revenue = CALCULATE( SUM( Table_Time_Series[Revenue] ), Table_Iterations[Flag] = 1, Table_Group[Group__Name] IN { "Group_A", "Group_B" } )
or condition directly in the measure
Total Revenue = CALCULATE( SUM( Table_Time_Series[Revenue] ), Table_Iterations[ID] >= 60004, Table_Iterations[ID] <= 10004, Table_Group[Group__Name] IN { "Group_A", "Group_B" } )
I'm not fully understood 2nd part of your question.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hello @parry2k ,
The alternate solution is perfect. That's exactly what I was looking for.
As to my 2nd question, basically, what did you mean when you said "change the columns and values based on your business logic" ?
@tk0501 oh that was more to tell you that you can change condition in measure based on what group or iteration id you want to select in the measure.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |