cancel
Showing results for
Did you mean:
Helper I

Calculate All rows related to other column

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

 OrderID Customer Y/N? Class Amount 111 ABC Y Apple 100 112 XYZ Y Apple 120 113 EPM Y Apple 345 114 YRP N Orange 120 115 PS N Apple 452 116 SC Y Apple 150 117 KB N Orange 244 118 RP N Mango 232 112 XYZ Y Mango 440 113 EPM Y Mango 550 114 YRP N Apple 330 115 PS N Mango 348 119 ABC N Mango 546 120 EPM N Mango 550 121 YRP Y Orange 332

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:

3 ACCEPTED SOLUTIONS
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()))

File is attached.

Kind regards, Steve.

Proud to be a Super User!

Super User I

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

see attached.

kr, steve.

Proud to be a Super User!

Super User I

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

Proud to be a Super User!

9 REPLIES 9
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()))

File is attached.

Kind regards, Steve.

Proud to be a Super User!

Helper I

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?

Super User I

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

Proud to be a Super User!

Helper I

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

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

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

Thanks a lot for supporting me on this.

Super User I

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

see attached.

kr, steve.

Proud to be a Super User!

Helper I

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

Thank you

Super User I

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

Proud to be a Super User!

Helper I

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.

Thank you again!

Super User I

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

Proud to be a Super User!

Announcements