Forum Discussion

atult's avatar
atult
Advocate I
5 years ago
Solved

Need a Calculated Measure/Column

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 Cha...
Need Help
Show and Tell
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    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's avatar
    Anand24
    5 years ago

    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
atult's avatar
atult
Advocate I
5 years ago

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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

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])
  )