topic Re: How to SumX over dynamic axis ? in DAX Commands and Tips

How to SumX over dynamic axis ?

k63 — Wed, 29 May 2024 09:11:09 GMT

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

Re: How to SumX over dynamic axis ?

xifeng_L — Wed, 29 May 2024 10:35:55 GMT

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

Re: How to SumX over dynamic axis ?

Rupak_bi — Fri, 31 May 2024 14:12:11 GMT

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

Re: How to SumX over dynamic axis ?

k63 — Mon, 03 Jun 2024 07:22:56 GMT

Hi, how can i share you my pbi example ?

PBI files upload is not supported here

Re: How to SumX over dynamic axis ?

Rupak_bi — Mon, 03 Jun 2024 08:38:50 GMT

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

Re: How to SumX over dynamic axis ?

k63 — Mon, 03 Jun 2024 08:49:03 GMT

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

Re: How to SumX over dynamic axis ?

Rupak_bi — Fri, 07 Jun 2024 09:04:09 GMT

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