Forum Discussion
How to SumX over dynamic axis ?
Hello, I'd like to create a measure that performs a SumX of an indicator according to the axes I put in my table, for example : Here the MixEffectSumX measurement is good, the total...
Ok, here is some sample data that a used to test :
A table Customer :
| CustomerID | CustomerCategory | CustomerSubCategory |
| 1 | Category1 | SubCategory11 |
| 2 | Category2 | SubCategory21 |
| 3 | Category1 | SubCategory12 |
| 4 | Category2 | SubCategory22 |
| 5 | Category2 | SubCategory21 |
| 6 | Category3 | SubCategory31 |
| 7 | Category3 | SubCategory31 |
| 8 | Category3 | SubCategory32 |
A Table Product :
| ProductID | ProductCategory | ProductSubCategory |
| 1 | Category1 | SubCategory11 |
| 2 | Category2 | SubCategory21 |
| 3 | Category1 | SubCategory12 |
| 4 | Category2 | SubCategory22 |
| 5 | Category2 | SubCategory21 |
A table Invoice :
| ProductID | CustomerID | Turnover |
| 1 | 1 | 5465 |
| 1 | 2 | 543 |
| 1 | 3 | 1213 |
| 1 | 4 | 154 |
| 2 | 5 | 5465 |
| 2 | 1 | 215 |
| 2 | 2 | 2424 |
| 2 | 3 | 104 |
| 2 | 4 | 4052 |
| 3 | 5 | 720 |
| 3 | 1 | 87 |
| 3 | 2 | 2 |
| 3 | 4 | 42 |
| 3 | 5 | 48 |
| 5 | 6 | 2 |
| 5 | 2 | 8 |
| 5 | 1 | 27 |
| 5 | 4 | 827 |
| 5 | 8 | 2 |
| 3 | 1 | 782 |
The goal is to have the "correct" total (which is when the measure contains SumX)
With the 5 following dax formulas :
MixEffect = SUM('Invoice'[Turnover]) - 50
MixEffectWithSumXCustomerCategory = SUMX(VALUES(Customer[CustomerCategory]), [MixEffect])
MixEffectWithSumXCustomerSubCategory = SUMX(VALUES(Customer[CustomerSubCategory]), [MixEffect])
MixEffectWithSumXProductCategory = SUMX(VALUES('Product'[ProductCategory]), [MixEffect])
MixEffectWithSumXProductSubCategory = SUMX(VALUES('Product'[ProductSubCategory]), [MixEffect])
Here I created 4 differents measures, and the goal is to have an unique measure, and keeping in mind that I have more than 2 tables (with a lot of column :/)
Thanks in advance
