Forum Discussion

t_guet01's avatar
t_guet01
Helper I
5 years ago
Solved

How do I calculate a custom column based on filtered rows?

Hey guys,   I am currently trying to calculate the revenue per country based on some kind of universal list price for the sake of comparability. I am facing transaction data per continent ("North A...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, t_guet01 

    Please check the below picture and the sample pbix file's link down below.

    It is for creating a new column.

     

     

    Solution Price CC =
    VAR currentproductid = Transactions[ProductID]
    VAR newtableorigin =
    SUMMARIZE (
    FILTER ( Transactions, Transactions[ProductID] = currentproductid ),
    Transactions[ContinentOfOrigin]
    )
    VAR newtable =
    SUMMARIZE (
    FILTER ( Transactions, Transactions[ProductID] = currentproductid ),
    Transactions[ContinentOfOrigin],
    Transactions[Price]
    )
    VAR tablerest =
    SUMMARIZE (
    FILTER (
    Transactions,
    Transactions[ProductID] = currentproductid
    && Transactions[ContinentOfOrigin] = "Rest"
    ),
    Transactions[ContinentOfOrigin],
    Transactions[Price]
    )
    RETURN
    SWITCH (
    TRUE (),
    "Rest" IN newtableorigin, AVERAGEX ( tablerest, Transactions[Price] ),
    AVERAGEX ( newtable, Transactions[Price] )
    )
     
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

t_guet01's avatar
t_guet01
Helper I
5 years ago

Hi Jihwan Kim,

perfect, thanks a lot!

just out of interest: do you know the respective functions in Power Query? So the equivalents for "summarize" etc.?

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

Hi, t_guet01 

Thank you for your feedback.

I think the function Groupby function in Power Query Editor is similar to the Summarize function in DAX.