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. 

[agigliotti]

did you try using "Dimension limits" somehow?

I hope it can helps.

[DaithiOK]

Hi,

Dimension limits will only limit my dimension (which is time) rather than my expression (which is number of projects). 

Thanks for the suggestion though

[sunny_talwar]

Would you be able to share a sample to show your issue?

[erikzions]

You should not do your record limiting in the dimension.  You should do it in the metrics.  That is where you will limit the rows that are in your table.  the dimension will be limited by default when the metrics are.  Add the set analysis you are doing to the calculation in your metric and this should clear it up.

[DaithiOK]

Hey, Thanks for responding to me.

As per my reply to the other user...I am not trying to limit my dimension. I am trying to limit my expression. My questions asks how can I do this outside of set analysis because set analysis is not enough. 

[DaithiOK]

Hey,

Yes I was thinking that I should do that as i don't think im explaining well enough. It will take a while for me to get an example though that mimics what i am doing. I will respond here when i do. Thanks. 

[sunny_talwar]

You might be able to use Sum(Aggr()), but it is difficult to give an exact expression.

[DaithiOK]

I included a sample that I hope highlights the issue. In one table on the left I have left it basic with all records included. In the table on the right i have applied the same logic i described above as to how I have limited to rows to meet certain conditions (positive variance > €1500 in this case but in my real case it is much more complicated). 

I am essentially trying to replicate the left line chart but with the elements in the table on the right ie. for a much smaller sample set. However in a line chart it just pulls in all records. 

Any help would be appreciated or if i can clarify further if there are questions. 

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(

If(Sum(TOTAL <ProductCategoryName, ProductLine, ProductName, ProductSubcategoryName> SalesForecast) - Sum(TOTAL <ProductCategoryName, ProductLine, ProductName, ProductSubcategoryName> SalesAmount) > 1500, Sum(SalesAmount))

, ProductCategoryName, ProductLine, ProductName, ProductSubcategoryName, YearMonth))