cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ManjunathaEP
Helper I
Helper I

Calculate All rows related to other column

Hi, Thank you in advance for helping me measure for the below sample data.

OrderIDCustomerY/N?ClassAmount
111ABCYApple100
112XYZYApple120
113EPMYApple345
114YRPNOrange120
115PSNApple452
116SCYApple150
117KBNOrange244
118RPNMango232
112XYZYMango440
113EPMYMango550
114YRPNApple330
115PSNMango348
119ABCNMango546
120EPMNMango550
121YRPYOrange332

I tried myself to get the measure in many ways, somehow I am not able to figure it out, please help me. I am able to arrive>>

1. the count of customers, who has "Y and N" = 3 (ABC, EPM, YRP)

2. the total count of orders for them is 6 (111,113,114,119,120 & 121)

But the requirement here is to calculate the count of rows and sum of Amounts for the "Class" from the table for the above mentioned 1 & 2. Expected results are as follows:

ManjunathaEP_0-1616157800223.png

 

3 ACCEPTED SOLUTIONS
stevedep
Super User I
Super User I

Hi,

Here you are:

 

Measure = SUMX(VALUES('Table'[Customer]), if    (CALCULATE(DISTINCTCOUNT('Table'[Y/N?]), ALL('Table'[Class])) = 2,CALCULATE(DISTINCTCOUNT('Table'[OrderID])),BLANK()))

 

stevedep_1-1616185873365.png

 

File is attached. 

Kind regards, Steve. 

 

View solution in original post

hi,

then it should be:

Measure = SUMX(SUMMARIZE('Table','Table'[Customer],'Table'[Class]), if    (CALCULATE(DISTINCTCOUNT('Table'[Y/N?]), ALL('Table'[Y/N?]), ALL('Table'[Class])) = 2,CALCULATE(DISTINCTCOUNT('Table'[OrderID])),BLANK()))

 

stevedep_0-1616228699603.png

see attached.

 

kr, steve. 

 

 

View solution in original post

I gues this is what you are looking for:

Measure = 
CALCULATE(
SUMX(SUMMARIZE('Table','Table'[Customer],'Table'[Class]), if    (CALCULATE(DISTINCTCOUNT('Table'[Y/N?]), ALL('Table'[Y/N?]), ALL('Table'[Class])) = 2,CALCULATE(DISTINCTCOUNT('Table'[OrderID])),BLANK())), 'Table'[Y/N?]="N")

View solution in original post

9 REPLIES 9
stevedep
Super User I
Super User I

Hi,

Here you are:

 

Measure = SUMX(VALUES('Table'[Customer]), if    (CALCULATE(DISTINCTCOUNT('Table'[Y/N?]), ALL('Table'[Class])) = 2,CALCULATE(DISTINCTCOUNT('Table'[OrderID])),BLANK()))

 

stevedep_1-1616185873365.png

 

File is attached. 

Kind regards, Steve. 

 

View solution in original post

Thank you, it is really helpful. However, my row counts are not matching to the total. Is possible to match the Totals with row count?  Also, I have noticed that there is a gap after "if" in the given measure, May I know the reason and how it works?

Can you send a screenshot or a (partial) file via PM? Difficult to help with your description of the issue alone. 

Thank you for your quick response, I fetched down the screenshot after applying the given measure in the original data. Columns are the types of "Class" in the sample data. Row total (506) is not matching with the Grand Total(474).

ManjunathaEP_0-1616227418736.png

Also, I would like to get a measure for the below:

I calculated the count orders for Class column with your given measure, where [Y/N?] is equal to Y and N and I got the answer as below

ManjunathaEP_1-1616227788164.png

Now, I would like to get the orders count from the above measure, where [Y/N?] is equal to "Y" only as like below: 

ManjunathaEP_2-1616227980088.png

Thanks a lot for supporting me on this.

 

hi,

then it should be:

Measure = SUMX(SUMMARIZE('Table','Table'[Customer],'Table'[Class]), if    (CALCULATE(DISTINCTCOUNT('Table'[Y/N?]), ALL('Table'[Y/N?]), ALL('Table'[Class])) = 2,CALCULATE(DISTINCTCOUNT('Table'[OrderID])),BLANK()))

 

stevedep_0-1616228699603.png

see attached.

 

kr, steve. 

 

 

View solution in original post

Hi, With reference to the previous measures and sample data, can we create a measure instead of selecting from the filter option as earlier. I mean, below mentioned results are expected from measure instead of selecting from the filter. I tried to get the same results without filter, but I failed to do so 

ManjunathaEP_0-1616408441187.png

Thank you

Hi, Its not clear what the question is. Can you rephrase?

btw measure was not updated yet:

Measure2 = SUMX(SUMMARIZE('Table','Table'[Customer],'Table'[Class]), if    (CALCULATE(DISTINCTCOUNT('Table'[Y/N?]), ALL('Table'[Class])) = 2,CALCULATE(SUM('Table'[Amount])),BLANK()))

Hi Steve, Thank you for your support.

I am able to get the solution with the earlier messages and it is working fine with the filter option. When I needed the selective results from the column of [Y/N?] like "N" only, I added the field [Y/N?] to the filters and it is working fine.

But, I want to get the new(direct) measure for the same results without selecting "N" from the filter option.

ManjunathaEP_0-1616420474009.png

Thank you again!

I gues this is what you are looking for:

Measure = 
CALCULATE(
SUMX(SUMMARIZE('Table','Table'[Customer],'Table'[Class]), if    (CALCULATE(DISTINCTCOUNT('Table'[Y/N?]), ALL('Table'[Y/N?]), ALL('Table'[Class])) = 2,CALCULATE(DISTINCTCOUNT('Table'[OrderID])),BLANK())), 'Table'[Y/N?]="N")

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors