Forum Discussion
Constant Sum Value in Matrix
Hello I would like some help in trying to get constant sum in my matrix.
I have a product table and a product sales table. I created a matrix that shows products and their sales from each quarter. I want to add another value column that shows a constant sum of particular product, for all of the products in that quarter. So the resulting visual should look something like this.
| Product | Product Type | Total Sales Q1 | Total Sales Q1 of Sports |
| A | Sports | 10 | 20 |
| B | Sports | 10 | 20 |
| C | Cooking | 1 | 20 |
| D | Books | 2 | 20 |
| E | Electronics | 3 | 20 |
I'm planning to use the value for a specific calculation. Thank you in advance for any future comments!
All the best,
Paisen
Thanks for the reply from PhilipTreacy , please allow me to provide another insight:
Hi paisen ,
I created some data:
Here are the steps you can follow：
1. Create calculated column.
Total Sales Q1 = IF( QUARTER('All_Table'[Date])=1&& 'All_Table'[Date]=MINX(FILTER(ALL('All_Table'),'All_Table'[Product]=EARLIER('All_Table'[Product])&&'All_Table'[Product Type]=EARLIER('All_Table'[Product Type])&&QUARTER('All_Table'[Date])=1),[Date]), SUMX( FILTER(ALL('All_Table'), QUARTER('All_Table'[Date])=1&&'All_Table'[Product]=EARLIER('All_Table'[Product])),[Sales]))Total Sales Q1 of Sports = IF( QUARTER('All_Table'[Date])=1&& 'All_Table'[Date]=MINX(FILTER(ALL('All_Table'),'All_Table'[Product]=EARLIER('All_Table'[Product])&&'All_Table'[Product Type]=EARLIER('All_Table'[Product Type])&&QUARTER('All_Table'[Date])=1),[Date]), SUMX( FILTER(ALL('All_Table'), 'All_Table'[Product Type] ="Sports"),[Total Sales Q1]))Total Sales Q1/Total Sales Q1 of Sports = DIVIDE( [Total Sales Q1],[Total Sales Q1 of Sports])2. Turn off – Row headers – Options – Stepped layout.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
5 Replies
Hi paisen
I'm not following how you get 20 for all the Products when you say you want a sum for each product ?
Please provide some source data and an example of how you are getting 20 as a result of your calculation.
Regards
Phil
Hello, sorry for the confusion. To clarify my goal is to add another value colum that shows a constant total sale of a particular product type. So in my example, I got 20 because the total sales of sports (Product A & B) is 20. I hope this helps.
Right, so you want a column for Sports, a column for Cooking, a column for Books etc? Seems like a lot of redundant data being displayed. You'd be better off to have a separate visual showing these totals, like a card for each Product Type or just a table?
Phil
Not really. I understand this could be shown in better ways but I just need this particular value. My goal is to be able to show (Total Sales Q1 / Total Sales Q1 of Sports). So in this case it would be
Product Type Total Sales Q1 Total Sales Q1 of Sports Total Sales Q1/Total Sales Q1 of Sports A Sports 10 20 0.50 B Sports 10 20 0.50 C Cooking 1 20 0.05 I know it doesn't make sense with this product example, but I'm just using these as placeholders for what calculation I'm trying to get. I just really need to get this constant sum value for the matrix.
Thanks for the reply from PhilipTreacy , please allow me to provide another insight:
Hi paisen ,
I created some data:
Here are the steps you can follow：
1. Create calculated column.
Total Sales Q1 = IF( QUARTER('All_Table'[Date])=1&& 'All_Table'[Date]=MINX(FILTER(ALL('All_Table'),'All_Table'[Product]=EARLIER('All_Table'[Product])&&'All_Table'[Product Type]=EARLIER('All_Table'[Product Type])&&QUARTER('All_Table'[Date])=1),[Date]), SUMX( FILTER(ALL('All_Table'), QUARTER('All_Table'[Date])=1&&'All_Table'[Product]=EARLIER('All_Table'[Product])),[Sales]))Total Sales Q1 of Sports = IF( QUARTER('All_Table'[Date])=1&& 'All_Table'[Date]=MINX(FILTER(ALL('All_Table'),'All_Table'[Product]=EARLIER('All_Table'[Product])&&'All_Table'[Product Type]=EARLIER('All_Table'[Product Type])&&QUARTER('All_Table'[Date])=1),[Date]), SUMX( FILTER(ALL('All_Table'), 'All_Table'[Product Type] ="Sports"),[Total Sales Q1]))Total Sales Q1/Total Sales Q1 of Sports = DIVIDE( [Total Sales Q1],[Total Sales Q1 of Sports])2. Turn off – Row headers – Options – Stepped layout.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
