How to limit line chart totals outside of set analysis?

[DaithiOK]

I am trying to recreate numbers in a table showing totals per month, in a line chart but my line chart takes in too many records and I don't know how to limit it.

My current logic is this:

I have a KPI that counts the number of objects that meet the criteria in the set analysis. Lets say its projects that have spent more than they should. The user will click on the KPI and a table will pop up showing all of the relevant projects, how much they spent, how much they are approved to spend and so on.

The way I limit the records that get pulled into the table is having a separate column called #Projects that replicates the KPI count so that each row in the table will have a 1 in that column if it matches the criteria. In the other expressions I then start by saying:

If(#Projects = 1,
  Set Analysis from KPI,
,0)

This prevents too many rows from populating the table as for some reason the suppress null values doesnt work properly in this regard.

My issue with the line chart is that i have no way of limiting the number of records that the expression pulls in in the same way i do in the table with if(#Projects=1) and so it basically pulls in everything so my totals are orders of magnitudes greater than they should be. 

My question is how can i limit the projects i pull in to the line chart such that only those that are in the table are represented?

I hope this makes sense because I am really stumped. 

[DaithiOK]

Oh amazing thank you so much. This is 90% of the way there but there is another quirk of my dashboard that limits this reponse. When I apply your logic you can see that it works perfectly for the current month (€110M is the answer i am looking for) but fails to populate for previous months. 

It may have something to do with the below code? When i make the adjustments to your code it ends up being this for my purposes;

Sum(  
    aggr(
          if( 
	      (Sum({<Various other Filters...,%ReportMonth1={$(vCurrentMonth)}>}[SalesForecast]))
	>
	      (Sum({<Various other Filters...,%ReportMonth1={$(vCurrentMonth)}>}[SalesAmount]))
						
              ,Sum({<VariousOtherFilters...,%ReportMonth1={">=$(vCurrentMonth-5)<=$(vCurrentMonth)"}>}SalesAmount)

              ),
[AGGREGATION PRODUCT COLUMNS], %ReportMonth1)
)

)

I suspect my %ReportMonth1 as an aggregation is forcing it to only look at the current month? (It is set in the current month and then we have other variables denoting previous months like in my code (vCurrentMonth-1) and so on).

By saying %ReportMonth1={">=$(vCurrentMonth-5)<=$(vCurrentMonth) I am trying to have data just for the last 6 months but am not sure why it only shows the current month? 

Does this make sense?

[sunny_talwar]

Try this

Sum({<VariousOtherFilters...,%ReportMonth1={">=$(vCurrentMonth-5)<=$(vCurrentMonth)"}>}  
    Aggr(
          If( 
	      (Sum({<Various other Filters...,%ReportMonth1={$(vCurrentMonth)}>}[SalesForecast]))
	>
	      (Sum({<Various other Filters...,%ReportMonth1={$(vCurrentMonth)}>}[SalesAmount]))
						
              ,Sum({<VariousOtherFilters...,%ReportMonth1={">=$(vCurrentMonth-5)<=$(vCurrentMonth)"}>}SalesAmount)

              ),
[AGGREGATION PRODUCT COLUMNS], %ReportMonth1)
)

)

[DaithiOK]

Thank you so much. 

It didn't work how you suggested but it worked when i put the code in after the first Sum() but then removed any mention of report month inside the if statement. 

Can i just ask how it works? I'm not sure i 100% understand it. If we remove ReportMonth1 everywhere except the first set analysis then we are basically saying the output of the If statement is to give us Sum of Sales for everything aggregated by the product categories. Then once we have taht we apply the outer sum but only bring in the months according to: 

>=$(vCurrentMonth-5)<=$(vCurrentMonth)

 I'm not sure why it only worked once i removed the ReportMonth1 inside the IF statement but i'm glad it does. Thank you very much for your responses. I will mark your first response as the solution because that does technically answer my original question. 

[sunny_talwar]

Before I answer why, can I have you look at this expression and see if this works?

Sum({<VariousOtherFilters...,%ReportMonth1={">=$(vCurrentMonth-5)<=$(vCurrentMonth)"}>}  
    Aggr(
          If( 
	      (Sum({<VariousOtherFilters...,%ReportMonth1={">=$(vCurrentMonth-5)<=$(vCurrentMonth)"}>} [SalesForecast]))
	>
	      (Sum({<VariousOtherFilters...,%ReportMonth1={">=$(vCurrentMonth-5)<=$(vCurrentMonth)"}>} [SalesAmount]))
						
              ,Sum({<VariousOtherFilters...,%ReportMonth1={">=$(vCurrentMonth-5)<=$(vCurrentMonth)"}>}SalesAmount)

              ),
[AGGREGATION PRODUCT COLUMNS], %ReportMonth1)
)

[DaithiOK]

Yes that works perfectly as well. The 2 options that work are like your text in that response and below where i tweaked your previous answer

Sum({<VariousOtherFilters...,%ReportMonth1={">=$(vCurrentMonth-5)<=$(vCurrentMonth)"}>}  
    Aggr(
          If( 
	      (Sum({<VariousFilters but not %ReportMonth1>} [SalesForecast]))
	>
	      (Sum({<VariousFilters but not %ReportMonth1>} [SalesAmount]))
						
              ,Sum({<VariousFilters but not %ReportMonth1>}SalesAmount)

              ),
[AGGREGATION PRODUCT COLUMNS], %ReportMonth1)
)

[sunny_talwar]

The reason this didn't work

Sum({<VariousOtherFilters...,%ReportMonth1={">=$(vCurrentMonth-5)<=$(vCurrentMonth)"}>}  
    Aggr(
          If( 
	      (Sum({<Various other Filters...,%ReportMonth1={$(vCurrentMonth)}>}[SalesForecast]))
	>
	      (Sum({<Various other Filters...,%ReportMonth1={$(vCurrentMonth)}>}[SalesAmount]))
						
              ,Sum({<VariousOtherFilters...,%ReportMonth1={">=$(vCurrentMonth-5)<=$(vCurrentMonth)"}>}SalesAmount)

              ),
[AGGREGATION PRODUCT COLUMNS], %ReportMonth1)
)

)

Was because of

%ReportMonth1={$(vCurrentMonth)}

This restricted your if statement expressions to just show value for the current month... so since the inner aggregation was more restrictive then the outer one, we didn't see the last 6 months you expected to see.

When we used this

%ReportMonth1={">=$(vCurrentMonth-5)<=$(vCurrentMonth)"}

both inner and outer aggregation were requesting to show last 6 months.

When we used

%ReportMonth1 (Ignore selection in the field)

inner aggregation included everything, but outer one restricted to show last 6 months which again worked.

If I were you, I would use %ReportMonth1={">=$(vCurrentMonth-5)<=$(vCurrentMonth)"}, unless there is a business need for which you need to use %ReportMonth1.

[DaithiOK]

Thanks again for your answers. Really helped me understand what i was doing wrong. 

[DaithiOK]

Hi Sunny,

I'm sorry about this but I'm having trouble implementing this similar logic in another format. I've tried adapting your logic here but can't seem to see why it isn't working. I'd be very grateful if you could take a look at my post here and maybe point me in the right direction. It's a similar problem of aggrs and an if statement.