cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SimonChung_GGGG Regular Visitor
Regular Visitor

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. 

 

image.png

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:

image.png

 

The question looks simple, but it struggle me for a time really.

 

 

Many many thanks
Simon Chung

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Measures by classification

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
Super User
Super User

Re: Measures by classification

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
SimonChung_GGGG Regular Visitor
Regular Visitor

Re: Measures by classification

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.
SimonChung_GGGG Regular Visitor
Regular Visitor

Re: Measures by classification

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

Super User
Super User

Re: Measures by classification

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

SimonChung_GGGG Regular Visitor
Regular Visitor

Re: Measures by classification

@Ashish_Mathur  

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

Super User
Super User

Re: Measures by classification

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
SimonChung_GGGG Regular Visitor
Regular Visitor

Re: Measures by classification

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? 

 

 

image.png

thanks a lot

Simon 

Super User
Super User

Re: Measures by classification

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
SimonChung_GGGG Regular Visitor
Regular Visitor

Re: Measures by classification

 

 

Please find my PBIX file

Download here 

 

Many thanks

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 262 members 2,620 guests
Please welcome our newest community members: