topic Re: Calculate All rows related to other column in DAX Commands and Tips

Calculate All rows related to other column

ManjunathaEP — Fri, 19 Mar 2021 12:45:15 GMT

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:

Re: Calculate All rows related to other column

stevedep — Fri, 19 Mar 2021 20:31:38 GMT

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.

Re: Calculate All rows related to other column

ManjunathaEP — Sat, 20 Mar 2021 06:01:30 GMT

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?

Re: Calculate All rows related to other column

stevedep — Sat, 20 Mar 2021 07:55:51 GMT

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

Re: Calculate All rows related to other column

ManjunathaEP — Sat, 20 Mar 2021 08:14:51 GMT

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.

Re: Calculate All rows related to other column

stevedep — Sat, 20 Mar 2021 08:25:26 GMT

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.

Re: Calculate All rows related to other column

ManjunathaEP — Mon, 22 Mar 2021 10:22:29 GMT

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

Re: Calculate All rows related to other column

stevedep — Mon, 22 Mar 2021 12:44:04 GMT

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

Re: Calculate All rows related to other column

ManjunathaEP — Mon, 22 Mar 2021 13:55:46 GMT

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!

Re: Calculate All rows related to other column

stevedep — Mon, 22 Mar 2021 15:46:27 GMT

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