Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
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
@Anonymous 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.
#I'M Not An Expert#
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.
Hi @Anonymous,
if you want I have another solution.
1) You create a relation between the two tables
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)
3) And you have your result.
Also the @Anonymous solution is valid. Choose the better option for your purpose.
Let me know if it works.
#I'M Not An Expert#
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?
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.
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,
@Anonymous 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.
#I'M Not An Expert#
Hello @Anonymous
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 :
After this, you could build you new Measures in the desktop like this :
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
Thanks Sebastien for replying. I would like to know where you include new measure? In the Table or in the power view.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
88 | |
75 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |