cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tk0501 Frequent Visitor
Frequent Visitor

Conditional sum calculation based on filter from parent tables

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:

  1. From Table_Group, Where Group_Name = selection (say GroupA and GroupB)
  2. From Table_Iterations, Where Rank_order = certain range (say between 1-2)
  • Using the above two filters...
  • Find all matching Revenues From Table_Time_Series for each year (ID100 and 105 would be added together, which results in new SumRevenue of $7050..

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,

 

Example Data Set.GIFExample Data Set

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Conditional sum calculation based on filter from parent tables

@tk0501

 

  • Table_Iterations[ID] IN {60004, 60005, 60006, 60007 }
  • What if I'm interested in Iterations[ID] ranges of 60004-61004 (1000 of them).  I would not want to manually type 1000 filters in. Is there a syntax where I can say 'in between' these two numbers?

 

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.





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




7 REPLIES 7
Super User
Super User

Re: Conditional sum calculation based on filter from parent tables

@tk0501 please provide pbix with sample data, shared the link thru onedrive/google drive.





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




tk0501 Frequent Visitor
Frequent Visitor

Re: Conditional sum calculation based on filter from parent tables

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!

 

Super User
Super User

Re: Conditional sum calculation based on filter from parent tables

@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" }
)




Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




tk0501 Frequent Visitor
Frequent Visitor

Re: Conditional sum calculation based on filter from parent tables

Thanks @parry2k ,

 

  1. Your DAX formula makes a lot of sense.  I have a few quesitons
    • Table_Iterations[ID] IN {60004, 60005, 60006, 60007 }
    • What if I'm interested in Iterations[ID] ranges of 60004-61004 (1000 of them).  I would not want to manually type 1000 filters in. Is there a syntax where I can say 'in between' these two numbers?
    • I assume this is a measure. 
      1. So, I created a Matrix
      2. Put Years as Columns
      3. Put Total Revenue as Values
      4. Change the visisuals to line chart. That appears to work!
  2. Table is structured to allow for input importing from my simulation runs.  Unfortunately, it is not a structure designed for outputs.
    • change the columns and values based on your business logic
    • Could you elabroate what you mean by this?  Create a new table?
    • I am very new to Power BI and trying to wrap my head around all the little steps.
    • If you could guide me by list out the steps I would need to take, that would be fantastic.
    • If I could be selfish, I would love for you to demonstrate your solution by editing the file that I shared but I know that's asking a lot.

 

Super User
Super User

Re: Conditional sum calculation based on filter from parent tables

@tk0501

 

  • Table_Iterations[ID] IN {60004, 60005, 60006, 60007 }
  • What if I'm interested in Iterations[ID] ranges of 60004-61004 (1000 of them).  I would not want to manually type 1000 filters in. Is there a syntax where I can say 'in between' these two numbers?

 

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.





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




tk0501 Frequent Visitor
Frequent Visitor

Re: Conditional sum calculation based on filter from parent tables

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" ?

 

Super User
Super User

Re: Conditional sum calculation based on filter from parent tables

@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. 





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.