Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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
Proud to be a Super User!
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.
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
Proud to be a Super User!
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.