Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ManjunathaEP
Helper II
Helper II

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
Memorable Member
Memorable Member

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
Memorable Member
Memorable Member

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. 

 

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. 

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors