Conditional sum calculation based on filter from parent tables

[tk0501]

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

[parry2k]

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

[tk0501]

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!

[parry2k]

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

[tk0501]

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.

[parry2k · Accepted solution]

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

[tk0501]

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

[parry2k]

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