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 all,
I am looking for a way to extract different indices from one table. Consider the table at the bottom, with which I want to calculate two sales indices:
- A general index for each retailer, regardless of product (total MAT sales x 100 / total MAT-1 sales)
- An index for each product at each retailer (MAT sales x 100 / MAT-1 sales)
What is the right DAX expression to do this? Is there a way to do this with one table, or do I need to create two tables? Right now, I can't find the right DAX expression and so far I ended up with indices having the exact same outcome.
Thanks in advance for your help!
Retailer Product Sales MAT Sales MAT-1 1 Alpha 36.765 33.067 1 Bravo 1.030.100 979.159 1 Charlie 1.627.002 1.529.596 2 Alpha 422.728 318.362 2 Bravo 1.475.902 1.616.456 2 Charlie 6.856.130 7.227.239 3 Alpha 173.111 140.780 3 Bravo 1.257.863 1.102.444 3 Charlie 2.480.445 2.804.763 4 Alpha 70.797 81.745 4 Bravo 607.618 564.895 4 Charlie 3.086.947 3.208.257 5 Alpha 90.355 83.760 5 Bravo 967.043 639.352 5 Charlie 2.484.531 2.416.448
Solved! Go to Solution.
Hi @BIfanatic,
Yes, it is. For example, you can use the formula below to create a new calculate column in the existing table to calculate the Total Sales MAT of Current Retailer.
Total Sales MAT of Current Retailer = CALCULATE ( SUM ( Table1[Sales MAT] ), FILTER ( ALL ( Table1 ), Table1[Retailer] = EARLIER ( Table1[Retailer] ) ) )
Regards
Hi @BIfanatic,
If I understand you correctly, you should be able to use the formulas below to create two calculate tables to get the indices for Retailer and Product in your scenario.
Retailer with Indices = SUMMARIZE ( Table1, Table1[Retailer], "Indices for Retailer", DIVIDE ( SUM ( Table1[Sales MAT] ), SUM ( Table1[Sales MAT-1] ) ) )
Product with Indices = SUMMARIZE ( Table1, Table1[Product], "Indices for Product", DIVIDE ( SUM ( Table1[Sales MAT] ), SUM ( Table1[Sales MAT-1] ) ) )
Note: just replace Table1 with your real table name.
Regards
@v-ljerr-msft Thanks for your input! The first index seems to work great.
The second is close but I need an index for each product and for each retailer. Your suggestion creates a table solely on product level. So, something like this:
Retailer Product Index 1 X 108 1 Z 118 2 Y 132 3 Y 122 3 Z 133
Do you know how to adjust the code you suggested? Thanks.
Hi @BIfanatic,
The formula below should work in that scenario.
Product with Indices = SUMMARIZE ( Table1, Table1[Retailer], Table1[Product], "Indices for Product", DIVIDE ( SUM ( Table1[Sales MAT] ), SUM ( Table1[Sales MAT-1] ) ) )
Regards
@v-ljerr-msft This is great, thanks a lot! Another question: is it possible to create a measured column inside an existing table, with which you can aggregate a measure based on a column inside that table?
Hi @BIfanatic,
Yes, it is. For example, you can use the formula below to create a new calculate column in the existing table to calculate the Total Sales MAT of Current Retailer.
Total Sales MAT of Current Retailer = CALCULATE ( SUM ( Table1[Sales MAT] ), FILTER ( ALL ( Table1 ), Table1[Retailer] = EARLIER ( Table1[Retailer] ) ) )
Regards
Dear all,
I am looking for a way to extract different indices from one table. Consider the table at the bottom, with which I want to calculate two sales indices:
- A general index for each retailer, regardless of product (thus: total MAT sales x 100 / total MAT-1 sales)
- An index for each product at each retailer (thus: MAT sales x 100 / MAT-1 sales)
What is the right DAX expression to do this? Is there a way to do this with one table, or do I need to create two tables? Right now, I can't find the right DAX expression and so far I ended up with indices having the exact same outcome.
Thanks in advance for your help!
Retailer Product Sales MAT Sales MAT-1 1 Alpha 36.765 33.067 1 Bravo 1.030.100 979.159 1 Charlie 1.627.002 1.529.596 2 Alpha 422.728 318.362 2 Bravo 1.475.902 1.616.456 2 Charlie 6.856.130 7.227.239 3 Alpha 173.111 140.780 3 Bravo 1.257.863 1.102.444 3 Charlie 2.480.445 2.804.763 4 Alpha 70.797 81.745 4 Bravo 607.618 564.895 4 Charlie 3.086.947 3.208.257 5 Alpha 90.355 83.760 5 Bravo 967.043 639.352 5 Charlie 2.484.531 2.416.448
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |