Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create a new column based on multiple row values

I have a table called Sales that includes the following columns: Year Country Service Vendor Revenue I want to create a new column AllRevenue that filters the Sales table on the current row v...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Anonymous 

    I tried to create a sample pbix file based on your explanation.

    Please check the below picture and the link down below.

    I suggest creating a new measure, but since your request is creating a new column, the below formula is for creating a new column.

     

     

    ALL Rev CC =
    VAR currentyear = 'Table'[Year]
    VAR currentcountry = 'Table'[Country]
    VAR currentservice = 'Table'[Service]
    RETURN
    CALCULATE (
    SUM ( 'Table'[Revenue] ),
    FILTER (
    'Table',
    'Table'[Year] = currentyear
    && 'Table'[Country] = currentcountry
    && 'Table'[Service] = currentservice
    )
    )
     
    And for your reference, the below is a new measure.
     
     
     

    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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thank you Jihwan_Kim! Your reply was very helpful.
Perhaps I'm thinking about the solution to my problem incorrectly. What I ultimately want is to compute a vendor's share of the markets they compete in. So, for instance, if Vendor A has operations in the United States and France, for a given service, their total addressable market (TAM) is the total revenue for that service in those countries, and their market share is their revenue for that service in those countries divided by that TAM. If Vendor B has offers the same service in the United States, Singapore, and Japan, they will have a different TAM. That's why the simple measure doesn't work, and it's why I chose the calculated column approach. I haven't been able to find a simpler method, but I'm open to suggestion 😉
Thanks again!

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

Hi, Anonymous 

Thank you for your feedback.

I am not sure how your real data model looks like, but perhaps I thought too simply.

I think your approach is correct. But later, please try to consider using measure, not the calculated column in the fact table.

If I may suggest,

- creating calculated columns in DIM tables is considered OK.

- creating calucated columns in Fact tables is considered "please consider one more time".

- I believe you already have many dim tables for this model, for instance, Date Table, Country Table, Service Table, Vendor Table, etc.. Together with using these Dim Tables, the measures become quite short and easy to write, and as a result, model size becomes compact.

- If your whole model size is not too big, then it is not a big concern. However, if your model size is big, then creating dim tables and measures is one of many ways to make the data model more efficient.

 

Sorry that I tried to make some suggestions without seeing your actual model, but I hope it helped.

Thanks.