Set Analysis to ignore Filter Pane

[Kat92]

Hi All,

I want to adjust a calculation in set analysis, so that it will ignore the filter Column1 from my report.

Column1 is in a filter pane in my sheet and the calculation is below:

Sum(Aggr(Value, Year_Month,[Name]))

Value column is found in Fact table, Name as well. The COlumn1 belongs in dimensional table connected with this Fact.

Could you please assist?

Thanks in advance

[AshutoshBhumkar]

Hi,

the question is not clear, can you please explain more with an example ?

Thanks,

Ashutosh

[Kat92]

Yes, of course.

So, this calculation aggregates the column Value per Year and Name. In the report I have a filter on Column1 and when I make a selection the number changes as it calculates only for the selected value of column1 but I want to always see the total of Column1.

In example this calculation gives me value 1100, COlumn1 has 3 Values.

400 of 1100 are for Value1, 600 for Value2 and 100 for Value3. I want to see always 1100 though no matter what I select in the filter pane for Column1

[AshutoshBhumkar]

Can you try below

Sum({<Column1=>}Value)

This will ignore the selection made in Column1

Thanks,

Ashutosh

[Kat92]

I Have tried the below but it does not work

{<Column1=>}Sum(Aggr(Value, Year_Month,[Name]))

and also 

{<Column1=>}Sum(Aggr({<Column1=>}Value, Year_Month,[Name]))

I think it is related with the Aggr function. Also, if I will not use the Aggr to sum per year and name then the sum is huge.

[MeehyeOh]

Hi, @Kat92 

Try this

Sum(Aggr(NoDistinct Sum(All <Column1> Value),Column1))

None selection,

.

Select Name = A,

.

Reply.Thanks.

[Kat92]

Apologies for my slightly delayed answer. I am trying the above but the number I get is huge and does not make sense. It is not the total of the table for the selected Year_Month (as you can see in my initial post I need to take into account the Year_Month and ignore the Column1.

However it is not the total of my table either. Not sure what the No Distinct Sum is doing.

[Gabbar]

Try this:
Sum(aggr(only({<Column1=>}Value),Value,Year_Month,Name))

[MeehyeOh]

Hi, @Kat92 

Is this your requirement?

 -Select '1' from Column1

Expression : Sum(Aggr(NoDistinct Sum(All <Column1,YearMonth> Value),Column1,YearMonth))

Reply, thanks

[Kat92]

Hi,

The screenshot is not very clear but I tried the calculation suggested and does not work.

What I need is similar to your initial solution:

Sum(Aggr(NoDistinct Sum(All <Column1> Value),Column1))

However the case for me does not work because of the fact table I have.

The issue is that Value numbers are repeated in my table (as I have other dimensions as well)

For example Year_Month=2023-10, Column1=A is not unique in my table as combination so the value for these dimensions is repeated and when I use the NoDistinct it will add up everything causing a huge value.

In the initial calculation I keep only the unique values for the dimensions I need and the problem is that I can not close the interaction with the COlumn1 Filter.

In other BI softwares you can do it with just closing the interaction from the slicer but in this case whatever I do I can not just ignore the filter.