Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
the question is not clear, can you please explain more with an example ?
Thanks,
Ashutosh
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
Can you try below
Sum({<Column1=>}Value)
This will ignore the selection made in Column1
Thanks,
Ashutosh
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.
Hi, @Kat92
Try this
Sum(Aggr(NoDistinct Sum(All <Column1> Value),Column1))
None selection,
.
Select Name = A,
.
Reply.Thanks.
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.
Try this:
Sum(aggr(only({<Column1=>}Value),Value,Year_Month,Name))
Hi, @Kat92
Is this your requirement?
-Select '1' from Column1
Expression : Sum(Aggr(NoDistinct Sum(All <Column1,YearMonth> Value),Column1,YearMonth))
Reply, thanks
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.