Forum Discussion

atult's avatar
atult
Icon for Advocate I rankAdvocate I
5 years ago
Solved

Need a Calculated Measure/Column

Hello Experts,
Below is the sample table for copying:

ChannelCompanyCategoryBrandRankValue
Channel1Company1Cat1Brand12100
Channel1Company2Cat1Brand2150
Channel2Company1Cat1Brand12200
Channel2Company2Cat1Brand21300

 

Image Representation:

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

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

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    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
    Icon for Super User rankSuper User
    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
    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      5 years ago

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

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