Getting Values More than equal to Total

[DS14]

Hi Experts,

I have calculated RM% . Now my requirement is based on selection the records which has the value greater than equal to total RM% on visible. For Example

In below image my Avg. RM% is 69.6% and now I want only those records in my table whose RM% Value is greater than equal to  69.6%.

Like from the image 82.6%,71.9%,339.5% and all.

Please help if anybody can!

Thanks in advance.

[pradosh_thakur]

if let's say your expression is avg(field) then may be if(avg(field)> avg(total field) ,avg(field),null()) .. then suppress zero.

[sunny_talwar]

What exactly is the expression behind RM%?

[DS14]

(sum(billing qty)*sum(material)) / sum(Billin Price) is the logic for RM%

[DS14]

Hi Pradosh
Its not working, Its giving same avg. RM% value against all values. like 69% against all records.

[sunny_talwar]

So, total average should be like this

Avg(TOTAL Aggr(
(Sum(billing qty)*Sum(material)) / Sum(Billin Price)
, YourChartDimensions))

[DS14]

Hi Sunny,

I have calculated the avg. now the requirement is on any selection (Year, Month, Any dimension filter) based on RM% I need show only those records which have the RM% value greater than equal to Total Avg. RM% .

[sunny_talwar]

Should be just like how @pradosh_thakur  mentioned...

If(

If(
(Sum(billing qty)*Sum(material)) / Sum(Billin Price) >=
Avg(TOTAL Aggr((Sum(billing qty)*Sum(material)) / Sum(Billin Price), YourChartDimensions)),
(Sum(billing qty)*Sum(material)) / Sum(Billin Price))

[DS14]

Hi Sunny it's not working for me,

I am calculating RM% using the below expression.

(

sum(
aggr(Sum({<
[Fiscal Year] = {">=$(=Max([Fiscal Year]))"}
,Procurement = {'E'}
,[Fiscal Month] //= {"$(=Month(Max(CalendarDate)))"}
,[Fiscal Quarter]
,Link_Date = {"<=$(=Date(Max(Link_Date),'DD-MM-YYYY'))"}
>}[Billing Qty]),PlantCode,[Material No],[Fiscal Month])
*
aggr(Sum({<
[Fiscal Year] = {">=$(=Max([Fiscal Year]))"}
,[Fiscal Month] //= {"$(=Month(Max(CalendarDate)))"}
,[Costing Variant] = {'YPC1'}
,[Fiscal Quarter]
,Link_Date = {"<=$(=Date(Max(Link_Date),'DD-MM-YYYY'))"}
>}BOM),PlantCode,[Material No],[Fiscal Month])
)
/
Sum({<
[Fiscal Year] = {">=$(=Max([Fiscal Year]))"}
,Procurement = {'E'}
//,[Fiscal Month] //= {"$(=Month(Max(CalendarDate)))"}
,[Fiscal Quarter]
,Link_Date = {"<=$(=Date(Max(Link_Date),'DD-MM-YYYY'))"}
>}[Base Price])

)

If i am using this expression in the way you suggested its not working.

Please suggest something on this.

[pradosh_thakur]

Hi Deepak,

A couple of questions before answering this

1: What is the total function you  have applied on the above mentioned expression, is it auto or average?

2: Do you have  PlantCode,[Material No],[Fiscal Month] as your dimension in the table ? If yes then aggr might not be needed in the case

I would request you to share a sample so we could provide you with a better solution. You are already using aggr 2 times. Like i said without seeing the dimension we can provide you an answer which might slow down your dashboard.

Thanks

Pradosh