How to SumX over dynamic axis ?

[k63]

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 is the expected number

But here the measurement only works for a specific axis (Item_Category)

My DAX formula : MixEffectSumX = SUMX(VALUES(Article[Item_Category]), [MixEffect])

The idea is that I would like to replace VALUES(Article[Item_Category]) with something that contains all the axes that I put in my table dynamically. Bearing in mind that I have ten or so dimensions and a hundred or so possible axes, the idea is that if I ever add axes in the future, I won't be obliged to modify this measure.

Does anyone have any clues?

Thanks in advance

[Rupak_bi]

please share sample data with some more axis you like to switch. will try

[k63]

 Hi, how can i share you my pbi example ? 

PBI files upload is not supported here

[Rupak_bi]

You can paste sample data here in reply with all possible axis variability. 

[k63]

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

[Rupak_bi]

Hi, 
@xifeng_L was right. This is not possible with the current data model. you need to restructur the model may be some other to optimiz the numbers of measure to create. Thanks

[xifeng_L]

Based on my experience, there should be no solution to achieve this idea.