Forum Discussion

sekharsahu25's avatar
sekharsahu25
Regular Visitor
4 years ago
Solved

Need hep

Hi I have to get the individual value of an id where as I am getting sum when I am joining the tables.

 

ID$Margin
9223$1,097,939.35$2,156,176.14
9230$54,829.31$1,746,478.98
9231$165,189.54$2,416,286.43
9232$11,209.56$1,063,664.75
9233$3,054.97$242,566.94
9234$979,928.18$1,904,609.81
9235$7,898.57$0.00
9236$13,517.66$302,949.23
9237$995.80$276,496.64
9238$1,576.19$317,147.18

 

Expected $ in below table

 

Dept IdAccnt$Margin Expected Value
9223Cost of Revenue6587636.1-715905TRUE10979391097939
9223Operating Revenue8783514.82872081FALSEFALSE 
9230Cost of Revenue603122.41-2180063TRUE54829.3154829.31
9230Operating Revenue548293.13926542FALSEFALSE 
9231Cost of Revenue1486705.86-485937TRUE165189.5165189.5
9231Operating Revenue2147464.022902224FALSEFALSE 
9232Cost of Revenue89676.48-863787TRUE11209.5611209.56
9232Operating Revenue78466.921927451FALSEFALSE 
9233Cost of Revenue15274.85-3677265TRUE3054.973054.97
9233Operating Revenue18329.823919832FALSEFALSE 
9234Cost of Revenue6859497.26-1317367TRUE979928.2979928.2
9234Operating Revenue8819353.623221976FALSEFALSE 
9235Cost of Revenue31594.28-5467.76TRUE7898.577898.57
9235Operating Revenue15797.145467.76FALSEFALSE 
9236Cost of Revenue148694.26-806623TRUE13517.6613517.66
9236Operating Revenue148694.261109572FALSEFALSE 
9237Cost of Revenue5974.8-649260TRUE995.8995.8
9237Operating Revenue5974.8925756.2FALSEFALSE 
9238Cost of Revenue9457.14-2886023TRUE1576.191576.19
9238Operating Revenue9457.143203170FALSEFALSE 
9239Cost of Revenue -1840396TRUE#N/A 
9239Operating Revenue 1884461FALSEFALSE 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi sekharsahu25 ,

     

     According to your expected output,

    • if [Accnt]="Cost of Revenue",then TRUE() and need to get the $ from Table 1 based on each iDept id and
    • if [Accnt]="Operating Revenue", then False()

    If so, please try:

    Duplicate = IF([Accnt]="Cost of Revenue",TRUE(),FALSE())
    $ = 
var _look=CONVERT( LOOKUPVALUE('Table 1'[$],[Dept ID],[Dept Id]) ,STRING)
return IF( [Accnt]="Cost of Revenue","FALSE", IF( _look=BLANK(),"#N/A",_look))

     

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi sekharsahu25 ,

     

    P;ease try:

    Duplicate = 
var _count=CALCULATE(COUNTROWS('Table 2'),ALLEXCEPT('Table 2','Table 2'[Dept Id]))
var _rank=RANKX(FILTER('Table 2',[Dept Id]=EARLIER('Table 2'[Dept Id])),[Accnt],,ASC)
return IF(_count>1 && _rank=1,TRUE())

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • sekharsahu25's avatar
    sekharsahu25
    Regular Visitor
    4 years ago

    Can anyone help on the above problem where I just need only look up value and the remaining should be blank

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    Your request is unclear. What are the two tables you need to merge and what is the expected outcome? 

    • sekharsahu25's avatar
      sekharsahu25
      Regular Visitor
      4 years ago

      Hi ,

       

      Thanks for the response

       

      Table 1

       

      Dept ID$Margin
      9223$1,097,939.35$2,156,176.14
      9230$54,829.31$1,746,478.98
      9231$165,189.54$2,416,286.43
      9232$11,209.56$1,063,664.75
      9233$3,054.97$242,566.94
      9234$979,928.18$1,904,609.81
      9235$7,898.57$0.00
      9236$13,517.66$302,949.23
      9237$995.80$276,496.64
      9238$1,576.19$317,147.18

       

       

       

      Table 2:

      Dept IdAccntExpenseCOR
      9223Cost of Revenue6587636-715905
      9223Operating Revenue87835152872081
      9230Cost of Revenue603122.4-2180063
      9230Operating Revenue548293.13926542
      9231Cost of Revenue1486706-485937
      9231Operating Revenue21474642902224
      9232Cost of Revenue89676.48-863787
      9232Operating Revenue78466.921927451
      9233Cost of Revenue15274.85-3677265
      9233Operating Revenue18329.823919832
      9234Cost of Revenue6859497-1317367
      9234Operating Revenue88193543221976
      9235Cost of Revenue31594.28-5467.76
      9235Operating Revenue15797.145467.76
      9236Cost of Revenue148694.3-806623
      9236Operating Revenue148694.31109572
      9237Cost of Revenue5974.8-649260
      9237Operating Revenue5974.8925756.2
      9238Cost of Revenue9457.14-2886023
      9238Operating Revenue9457.143203170
      9239Cost of Revenue -1840396
      9239Operating Revenue 1884461

      Now I have to get table 1 $ id column in table 2 without getting duplicated.Means only one time it should get mapped in table 2 based on the Dept Id something similar like below. I created the duplicate column for my purpose.You can ignore it.

       

      Dept IdAccntExpenseCORDuplicate$
      9223Cost of Revenue6587636-715905TRUE1097939
      9223Operating Revenue87835152872081FALSEFALSE
      9230Cost of Revenue603122.4-2180063TRUE54829.31
      9230Operating Revenue548293.13926542FALSEFALSE
      9231Cost of Revenue1486706-485937TRUE165189.5
      9231Operating Revenue21474642902224FALSEFALSE
      9232Cost of Revenue89676.48-863787TRUE11209.56
      9232Operating Revenue78466.921927451FALSEFALSE
      9233Cost of Revenue15274.85-3677265TRUE3054.97
      9233Operating Revenue18329.823919832FALSEFALSE
      9234Cost of Revenue6859497-1317367TRUE979928.2
      9234Operating Revenue88193543221976FALSEFALSE
      9235Cost of Revenue31594.28-5467.76TRUE7898.57
      9235Operating Revenue15797.145467.76FALSEFALSE
      9236Cost of Revenue148694.3-806623TRUE13517.66
      9236Operating Revenue148694.31109572FALSEFALSE
      9237Cost of Revenue5974.8-649260TRUE995.8
      9237Operating Revenue5974.8925756.2FALSEFALSE
      9238Cost of Revenue9457.14-2886023TRUE1576.19
      9238Operating Revenue9457.143203170FALSEFALSE
      9239Cost of Revenue -1840396TRUE#N/A
      9239Operating Revenue 1884461FALSEFALSE

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi sekharsahu25 ,

     

     According to your expected output,

    • if [Accnt]="Cost of Revenue",then TRUE() and need to get the $ from Table 1 based on each iDept id and
    • if [Accnt]="Operating Revenue", then False()

    If so, please try:

    Duplicate = IF([Accnt]="Cost of Revenue",TRUE(),FALSE())
    $ = 
var _look=CONVERT( LOOKUPVALUE('Table 1'[$],[Dept ID],[Dept Id]) ,STRING)
return IF( [Accnt]="Cost of Revenue","FALSE", IF( _look=BLANK(),"#N/A",_look))

     

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • sekharsahu25's avatar
      sekharsahu25
      Regular Visitor
      4 years ago

      Thanks Eyelyn,

       

      The scenario is exactly the same but I have taken the Dept Id as the condition for true and false ,not the accnt Id.

       

      If dept Id is reflecting more than once it should be false

       

      9223 : True

      9223 : False

       

      Based on the above I should do look up.Remaining all are same steps as you did.

       

      Thank you so much for your response anyways.Its so much helpful and helped me a lot!!!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi sekharsahu25 ,

     

    P;ease try:

    Duplicate = 
var _count=CALCULATE(COUNTROWS('Table 2'),ALLEXCEPT('Table 2','Table 2'[Dept Id]))
var _rank=RANKX(FILTER('Table 2',[Dept Id]=EARLIER('Table 2'[Dept Id])),[Accnt],,ASC)
return IF(_count>1 && _rank=1,TRUE())

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi sekharsahu25 ,

         

        Glad to help you😊

         

        Best Regards,
        Eyelyn Qin