Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Filtering for outliers from sales data

Hi    I am new to PBI and would like to achieve the following -    There are 2 tables,    Table 1 -  Revenue data as below Order Type Qtr Revenue_USD Margin % Outlier 1 PowerEdge ...
Need Help
Show and Tell
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

So something like the following?

 

Outlier = 
  VAR __Margin1 = 
    MAXX(
      FILTER(
        RELATEDTABLE('Table 2'),
        'Table 2'[Qtr] = 'Table 1'[Qtr]
      ),
      [Margin for First Quarter]
    )
  VAR __Margin2 = 
    MAXX(
      FILTER(
        RELATEDTABLE('Table 2'),
        'Table 2'[Qtr] = 'Table 1'[Qtr]
      ),
      [Margin b/w 1st and 3rd Quart]
    )
  VAR __MarginMax = MAXX( { __Margin1, __Margin2 },[Value])
  VAR __MarginMin = MINX( { __Margin1, __Margin2 },[Value])
RETURN
  IF( 
    'Table 1'[Revenue USD] >= __MarginMin && 'Table 1'[Revenue USD] <= __MarginMax,
    FALSE(),
    TRUE()
  )