Need a Calculated Measure/Column

[atult]

Hello Experts,
Below is the sample table for copying:

Channel	Company	Category	Brand	Rank	Value
Channel1	Company1	Cat1	Brand1	2	100
Channel1	Company2	Cat1	Brand2	1	50
Channel2	Company1	Cat1	Brand1	2	200
Channel2	Company2	Cat1	Brand2	1	300

Image Representation:

Requirement: Calculate the Difference column/measure. Basically it is Company1 Value - Company2 Value for the same Channel.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Column = if(Data[Company]="Company2",BLANK(),Data[Value]-CALCULATE(SUM(Data[Value]),FILTER(Data,Data[Channel]=EARLIER(Data[Channel])&&Data[Company]="Company2")))

[Anand24 · Accepted solution]

Hi @atult ,
Try the below calculated measure:

New Measure = 
MAXX(
    FILTER(
        ALL('Table'), 
        'Table'[COMPANY] <> "Company1" && 'Table'[Channel] in VALUES('Table'[Channel])
        ) , 
        'Table'[Value]
) 
- 
MAXX(
    FILTER(
        'Table', 
        'Table'[COMPANY] = "Company1" && 'Table'[Channel] in VALUES('Table'[Channel])
    ), 
    'Table'[Value]
)

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Column = if(Data[Company]="Company2",BLANK(),Data[Value]-CALCULATE(SUM(Data[Value]),FILTER(Data,Data[Channel]=EARLIER(Data[Channel])&&Data[Company]="Company2")))

[atult]

@amitchandak, @Greg_Deckler , @parry2k , @Ashish_Mathur 
Can you please look at this urgent requirement and help me?

[Greg_Deckler]

@atult Try:

Column = 
  VAR __Channel = [Channel]
  VAR __Company = [Company]
  VAR __Category = [Category]
RETURN
  IF(
    __Company = "Company2",
    BLANK(),
    [Value] - MAXX(FILTER('Table',[Channel]=__Channel && __Company = "Company2" && __Category = [Category]),[Value])

[atult]

@Greg_Deckler,
Thanks for the reply !
But this is giving me the exact same value in the new column.

[Greg_Deckler]

@atult Sorry, slight syntax error:

Column = 
  VAR __Channel = [Channel]
  VAR __Company = [Company]
  VAR __Category = [Category]
RETURN
  IF(
    __Company = "Company2",
    BLANK(),
    [Value] - MAXX(FILTER('Table',[Channel]=__Channel && [Company]="Company2" && [Category]=__Category),[Value])
  )