Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Return Value from other column

Hey,

Can you help me with this problem that I face here?

 

I have two tables as shown below:

 

any idea how to get value as the expected column?

 

thank you

 
Need Help
Show and Tell
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    Based on your description, you may create a measure as below.

     

    Pricing Value = 
var _date = SELECTEDVALUE(Takeup[Date])
var _type = SELECTEDVALUE(Takeup[Type])
return
CALCULATE(
    AVERAGE(Pricing[Value]),
    FILTER(
        ALLSELECTED(Pricing),
        Pricing[Date] = _date&&
        Pricing[Type] = _type
    )
)

     

     

    Result:

     

    Best Regards

    Allan

     

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

4 Replies

  • vivran22's avatar
    vivran22
    Community Champion
    6 years ago

    Hello Anonymous ,

     

    You may try following as New table:

     

    Takeup Table = 
SUMMARIZE(
    PricingData,
    PricingData[ID],
    PricingData[Type],
    "Takeup Value",AVERAGE(PricingData[Expected]),
    "Expected",AVERAGE(PricingData[Value])
)

     

     

    Alternatively, you could also use Group By feature in Power Query.

     

    Cheers!
    Vivek

    If it helps, please mark it as a solution
    Kudos would be a cherry on the top 🙂

    https://www.vivran.in/

    Connect on LinkedIn

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Sorry, I guess I wrongly asking the question.

      The pricing value is averaged to the take up table, based on type.

      Actually there also a date column on the table.

       

      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        6 years ago

        Hi, Anonymous 

         

        Based on your description, you may create a measure as below.

         

        Pricing Value = 
var _date = SELECTEDVALUE(Takeup[Date])
var _type = SELECTEDVALUE(Takeup[Type])
return
CALCULATE(
    AVERAGE(Pricing[Value]),
    FILTER(
        ALLSELECTED(Pricing),
        Pricing[Date] = _date&&
        Pricing[Type] = _type
    )
)

         

         

        Result:

         

        Best Regards

        Allan

         

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

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    If you take Simply the Avg or Value and expected it will work in visuals


    In pricing table
    new column Expected= if(Table[Type]="A",5, if (Table[Type]="B",10,15))

    New Table =
    summarize(Table,Table[ID],Table[Type],"Take up value",Max(Table[Expected]),"Expected",max(Table[Expected]))