Constant Sum Value in Matrix

[paisen]

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

[v-yangliu-msft · Accepted solution]

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

[PhilipTreacy]

@paisen 

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

[paisen]

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.

[PhilipTreacy]

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

[paisen]

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.