Create a new column based on multiple row values

[CloudHerder]

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 values of Year, Country, and Service and then calculates the SUMX of Revenue. Let's say this is one row of the table:

Year	Country	Service	Vendor	Revenue
2020	Canada	Curling supplies	Stones R Us	350

I want a new column AllRevenue that for this row will show all 2020 revenue in Canada for Curling supplies, summed for all vendors. Later, I'll use this column to determine market share for each Vendor. I'm not getting the syntax right, though.

I've tried creating a VAR table using SELECTCOLUMNS with FILTER, but I can't get the syntax right. I'm also hoping there's a more efficient way to do this than computing a temp table for each row as I iterate through the table.

I can provide additional details if needed.

Any help is greatly appreciated!

[Jihwan_Kim · Accepted solution]

Hi, @CloudHerder 

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.

https://www.dropbox.com/s/qp8d5cxldv4csax/cloudherder.pbix?dl=0 

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

[CloudHerder]

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]

Hi, @CloudHerder 

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.

[CloudHerder]

I'm the one who should apologize for not providing the full details of my model. My dataset is moderately large (~400K rows). The services are actually structured hierarchically. There is also an industries dimension that I didn't include. I need to be able to filter by any combination of fields, so the calculated column provides a simple, if inefficent, solution. My market share calculation is just
Market Share :=
DIVIDE( SUM(Sales[Revenue]), SUM(Sales[All Rev CC], 0)

This works for whatever filter context I apply. Once I get it working just the way I want, I'll try another version with linked DIM tables that are more elegant. I'm just excited to get this working in DAX at all. I was going to fall back on Python, but I prefer Power BI for sharing and interactivity.

I truly appreciate your help and advice.