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 file for download
https://gbil-my.sharepoint.com/:u:/p/simon_chung/EeDrG4mY275IsXZCxPWAPsIBAVhcBqgydIfn0tkGvUTKOQ
There is a CODE contains A, B and the others, in this case we will only focus on A & B.
Customers will be classified into
A only - Yellow
B only - Orange
Mixed: A - Green
Mixed: B - Blue
The expected result is to create 4 Measures to get the sum of A only, B only, Mixed: A, Mixed: B
Note the results will vary with the time of selection, so it is no way to create Column to solve.
Expected result:
The question looks simple, but it struggle me for a time really.
Many many thanks
Simon Chung
11 Replies
Hi,
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