cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TeeTreeThree Helper II
Helper II

Sum (filter by same date)

Dear experts,

 

I would like to sum the sales by the same date in another table to calculate market share.

 

Example:

Posting Date     Sales

1 Jan 16            3

1 Jan 16            2

2 Jan 16            7

2 Jan 16            1

 

 

Date                  Market Sales            New Column

1 Jan 16            55                             Sales (3 + 2) / Market Sales (55)

2 Jan 16            60                             Sales (7 + 1) / Market Sales (60)

 

 

Appreciate your time to help. Thank you in advance,

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Sebastien Resolver III
Resolver III

Re: Sum (filter by same date)

You add your measure in the Power BI desktop, not in the table. You right click and select new measure. After that, you will be able to change the name (by default it should write "measure") and enter the formula.

 

Sébatien

View solution in original post

B129 Continued Contributor
Continued Contributor

Re: Sum (filter by same date)

@TeeTreeThree if you want a monthly view you can:

 

1) Create a calculated column (in the same table of the "NewColumn") to show the month.

 

2) Then you can put "Month" and "NewColumn" into a visualization, and it automatically recalculate the value.

 

SumFilter (4).png

 

#I'M Not An Expert#

View solution in original post

B129 Continued Contributor
Continued Contributor

Re: Sum (filter by same date)

I've used the FORMAT() Function to show the month's name. If you want to display something else you can change the format (ex. "mmmm") using these.

View solution in original post

8 REPLIES 8
Sebastien Resolver III
Resolver III

Re: Sum (filter by same date)

Hello @TeeTreeThree

 

This may be a solution,

First thing to do is to linked the table with the time dimension. So you should add a time table.

Then you build your relationship like this :

 

Capture d’écran 2016-03-08 à 09.10.38.png

After this, you could build you new Measures in the desktop like this :

 

Capture d’écran 2016-03-08 à 09.11.21.png

Don't forget to wisely choose the column datetype (eg : Decimal number for new Measure, Integer with summary for Sales & Market Sales).

 

Hope it help you

 

Sébastien

B129 Continued Contributor
Continued Contributor

Re: Sum (filter by same date)

Hi @TeeTreeThree,

 

   if you want I have another solution.

 

1) You create a relation between the two tables

SumFilter (1).png

 

2) After that you can create the calculated column (I put it in the "Sales" table because in the other I cannot use the RELATED() function)

SumFilter (3).png

 

3) And you have your result.

 

Also the @Sebastien solution is valid. Choose the better option for your purpose.

Let me know if it works.

 

#I'M Not An Expert#

TeeTreeThree Helper II
Helper II

Re: Sum (filter by same date)

Thanks Sebastien for replying. I would like to know where you include new measure? In the Table or in the power view.

TeeTreeThree Helper II
Helper II

Re: Sum (filter by same date)

Hi B129, I see your reply and thanks a lot for correcting my mistake. So my mistake is I am supposed to use related in the sales table.

 

Then another question will be:

In the example shown there is daily transaction (Sales) and daily market sales. How do I do it if I want to have monthly market share instead of daily market share?

 

 

Sebastien Resolver III
Resolver III

Re: Sum (filter by same date)

You add your measure in the Power BI desktop, not in the table. You right click and select new measure. After that, you will be able to change the name (by default it should write "measure") and enter the formula.

 

Sébatien

View solution in original post

B129 Continued Contributor
Continued Contributor

Re: Sum (filter by same date)

@TeeTreeThree if you want a monthly view you can:

 

1) Create a calculated column (in the same table of the "NewColumn") to show the month.

 

2) Then you can put "Month" and "NewColumn" into a visualization, and it automatically recalculate the value.

 

SumFilter (4).png

 

#I'M Not An Expert#

View solution in original post

B129 Continued Contributor
Continued Contributor

Re: Sum (filter by same date)

I've used the FORMAT() Function to show the month's name. If you want to display something else you can change the format (ex. "mmmm") using these.

View solution in original post

TeeTreeThree Helper II
Helper II

Re: Sum (filter by same date)

Hi Sebastien and B129,

 

My apology for going MIA, no time to come back and check my posting.

 

I really appreciate your guidance and teaching, I learnt a lot from you guys. Thanks for being patient with me as I am new to Power BI.

 

 

Warmest regards,

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors