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...
Rupak_bi
Super User
Super Userplease share sample data with some more axis you like to switch. will try
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.
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]) - 50MixEffectWithSumXCustomerCategory = 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