Forum Discussion
Measures by classification
Hi Experts, I have a question that struggle me for a time, would anyone can help me to solve? I would like to create 4 measures (It's 2 types of measure in fact) Please find the raw sample f...
Ashish_Mathur
Super User
Super UserHi,
Your expected result seems incorrect. On creating a simple Pivot Table (see image below), there is no Customer (for any Item) which has both A and B. Once you correct your source data, these measures should works for "Only A" and "Only B"
Only A = SUMX(FILTER(SUMMARIZE('Table','Table'[Customer],"ABCD",CALCULATE(DISTINCTCOUNT('Table'[code]),all('Table'[Item])),"EFGH",CALCULATE([Total],'Table'[Code]="A")),[ABCD]=1),[EFGH])
Only B = SUMX(FILTER(SUMMARIZE('Table','Table'[Customer],"ABCD",CALCULATE(DISTINCTCOUNT('Table'[code]),all('Table'[Item])),"EFGH",CALCULATE([Total],'Table'[Code]="B")),[ABCD]=1),[EFGH])
- Not really, the data source and expected result are correct, the result get from pivot table as well.
Customer 03,04,05,08 have both code A and B.
Pls note that
- Same customer and item have both code A and B is no necessary
- The classification is derived from customers and types.
- The result is grouped by items. Hi Ashish_Mathur
I would like to make this question simple, I now just want the Yellow part - A Only.
such that the sum of amount will exclude those customers have both A and B. hope that you can help, many thanks!Simon
- Ashish_Mathur
Ashish_Mathur
You're so great! It's exactly what I want. a million thanks ^^