Measures by classification

[Anonymous]

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

[Ashish_Mathur]

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])

[Anonymous]

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 · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

@Ashish_Mathur  

You're so great! It's exactly what I want. a million thanks ^^

[Ashish_Mathur]

You are welcome.

[Anonymous]

Hi @Ashish_Mathur  

Sorry, need your help again. 

There is something strange in the real data, I'm not sure if the no. of record too big.

I found that the slicer cannot take control. 

For example. the amount of SGBM0000054 (204) should go to "A only" (A = "01", B = "02")

as there is no amount for "02"
but now it go to Mixed A. do you know what's the problem and how to fix it? 

thanks a lot

Simon 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

Please find my PBIX file

Download here 

Many thanks

[Ashish_Mathur]

Hi,

Those answers seem correct to me.  The 3 items that you see in Table2 : B-B-VFSM, B-B-VFSPM and B-S-PBS(S) have codes other than 1 and 2 (see the 3 screenshots below).  In the [A only] measure, we have excluded "Other".  There is no Other entry in the Code column in your dataset.  I guess you want to exlude all except codes 01 and 02.  If that indeed is the case, then the formula will have to be modified.

[Anonymous]

Oh So sorry for my careless mistake 

Thanks so much

[Anonymous]

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.