topic Re: Calculating market shares in different cities in one country in DAX Commands and Tips

Calculating market shares in different cities in one country

Anonymous — Fri, 18 Mar 2022 08:09:14 GMT

Hi, I have the following tables:
Company list = is a list of companies, cities etc.
Company data = contains, dates, companies,revenue etc.
These are connected with a "many to many" relationship, with cross filter direction ="both" based on the company name.

For calculating the total market share I have the following expression, which works great:
MS% All companies = DIVIDE(SUM('company Data'[revenue]),CALCULATE(SUM('company Data'[revenue]),REMOVEFILTERS('company Data'[company])))

My problem:
I want to have an expression which calculates the market share of the companies in one city. I have tried this formula, but it doesnt seem to work:

MS % companies by city DIVIDE(SUM('company Data'[revenue]),CALCULATE(SUM('company Data'[revenue]),'company company List'[City]="City 1"))

Does anyone have a solution for this?

Appriciate any help!

Re: Calculating market shares in different cities in one country

Anonymous — Fri, 18 Mar 2022 14:38:30 GMT

Hi, im rephrasing my question. Look away from the above.

New question:
How can I make å measure that shows the total revenue by a specified city? Lets say we could put in "city" as a constant reference in the DAX (i.e "City 1").

I want my outcome to look like the sums in "Total revenue by city" when I put it in a matrix visual:

Re: Calculating market shares in different cities in one country

Whitewater100 — Fri, 18 Mar 2022 21:08:54 GMT

Hello:

Something like this as calc col

City Revenue =
vcity = SELECTEDVALUE(TableName[City])

return
Calculate(SUM(Tablename[City],
Tablename[city] = vcity)
)

Re: Calculating market shares in different cities in one country

Anonymous — Mon, 21 Mar 2022 20:50:19 GMT

Hi, thanks for your answer Whitewater100 but I cant make it work.
Any other suggestions? amitchandak Jihwan_Kim AlexisOlson 🙂

Re: Calculating market shares in different cities in one country

Whitewater100 — Mon, 21 Mar 2022 21:12:22 GMT

Hi: This will work as calculated column..

City Revenue =

CALCULATE(SUM(City[Revenue]),

ALLEXCEPT(City,City[City]))

Re: Calculating market shares in different cities in one country

Whitewater100 — Mon, 21 Mar 2022 21:14:12 GMT

City is name of table and column of table..

Re: Calculating market shares in different cities in one country

Anonymous — Tue, 22 Mar 2022 12:03:50 GMT

Whitewater100 Thank you very much! I didnt mention that I need the total revenue for each city per day. Do you think that this is possible to do?

Re: Calculating market shares in different cities in one country

Whitewater100 — Tue, 22 Mar 2022 12:40:34 GMT

Yes, if you have a sample data I can show you. We would need to put in a date table to make it easier.

Are you able to mark this first answer as solution for first question? Thanks..

Re: Calculating market shares in different cities in one country

Anonymous — Tue, 22 Mar 2022 14:34:37 GMT

Whitewater100 Hi, thank you so much! I cant see that im able to share pbix file or excel, but mye sample looks like this:

Re: Calculating market shares in different cities in one country

Whitewater100 — Tue, 22 Mar 2022 14:40:30 GMT

Your welcome:-)

Re: Calculating market shares in different cities in one country

Whitewater100 — Wed, 23 Mar 2022 13:28:29 GMT

Hi:

OK, I just noticed your email. Is it ok with you I can look at it a bit later today? I have commitment this am. Thanks!

Re: Calculating market shares in different cities in one country

Whitewater100 — Wed, 23 Mar 2022 19:45:16 GMT

Hi Kiisj:

Yes, no problem. Please see link. I added a couple tables and two measures to show how you can obttain the same results using the modeling approach. I'll paste the calculated column below.

https://drive.google.com/file/d/1BdL_BzKkEJbW7cBaiwq_MMIqaxkNFc_L/view?usp=sharing

Revenue City/Day =

var dazs = CityRev[Date]

return

CALCULATE(

SUM(CityRev[Revenue]),

ALLEXCEPT(CityRev,CityRev[City]),

CityRev[Date] = dazs)