topic Re: SUM on 2 columns ignore the third but filter third in DAX Commands and Tips

SUM on 2 columns ignore the third but filter third

sun-sboyanapall — Mon, 18 Apr 2022 20:06:57 GMT

Hello,

This might be a silly question, but having issues to implement this in a measure.

I have a table with 4 columns

Name	Date	Type	Metric
A	12/1/2020	A	1
A	12/1/2020	B	5
A	12/1/2020	C	6
A	12/1/2020		1
B	12/1/2020	A	1
B	12/1/2020	B	3
B	12/1/2020		7

Here I want to sum(Metric) but ignore Type from it

Result is something like Sum(Metric) = 13, Average(Metric) = 13, Median(Metric) = 13 for Name A

If I follow the traditional approach the sum is still 13 but my average and median are screwed by it. I can just do

SUMMARIZE(Name, Date, Metric, SUM(Metric)) but the issue is I want to filter the data by Type.

so, If I filter by type A and B Result is something like Sum(Metric) = 6, Average(Metric) = 6, Median(Metric) = 6 for Name A

Thanks,

Re: SUM on 2 columns ignore the third but filter third

Whitewater100 — Mon, 18 Apr 2022 20:30:50 GMT

Hi:

Can you just use this type of measure?

Name A Sum = CALCULATE(SUM('Table'[Metric]),'Table'[Name] = "A")

Name A Avg = CALCULATE(AVERAGE('Table'[Metric]),'Table'[Name] = "A")

You could set up two dimension tables for this data table, to help with filtering.

Table Name Name = A & B

Table Name Type = A,B, C

I hope this is what you are asking about..

Re: SUM on 2 columns ignore the third but filter third

sun-sboyanapall — Mon, 18 Apr 2022 20:36:42 GMT

I have 100's of names so tis is not feasible.

Re: SUM on 2 columns ignore the third but filter third

tamerj1 — Tue, 19 Apr 2022 05:00:47 GMT

Hi sun-sboyanapall

would you please help explain further what is point of having the sum, average and median all having the same value? If so just use the sum for the three measure only change the name. But what's the point?

Re: SUM on 2 columns ignore the third but filter third

sun-sboyanapall — Tue, 19 Apr 2022 14:53:43 GMT

Hello Tamerj,

Sure, A Name can have different dates. In that case the average will be average grouped by date and Median will be median by Date

Example:

Name	Date	Type	Metric
A	11/1/2021	A	1
A	11/1/2021	A	1
A	11/1/2021	A	2
A	11/5/2021	B	3
A	12/6/2021	B	1

In this case Sum is 8, Average is 2.66, Median is 3.

Hope that helps.

Re: SUM on 2 columns ignore the third but filter third

tamerj1 — Wed, 20 Apr 2022 05:00:49 GMT

sun-sboyanapall
I'm sorry but this is getting me even more confused. In the original post you mentioned:
"Result is something like Sum(Metric) = 13, Average(Metric) = 13, Median(Metric) = 13 for Name A"
"so, If I filter by type A and B Result is something like Sum(Metric) = 6, Average(Metric) = 6, Median(Metric) = 6 for Name A"
Please provide more details and provide a sample of expected results in the expected visual (table, chart, matrix, card, etc..)?

Re: SUM on 2 columns ignore the third but filter third

Anonymous — Thu, 21 Apr 2022 06:33:17 GMT

Hi sun-sboyanapall ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a measure as below to get Sum(Metric)

Sum(Metric) = CALCULATE ( SUM ( 'Table'[Metric] ), ALLEXCEPT ( 'Table', 'Table'[Name] ) )

2. Create a measure as below to get Average(Metric)

Average(Metric) = CALCULATE ( DIVIDE ( [Sum(Metric)], CALCULATE ( DISTINCTCOUNT ( 'Table'[Date] ) ) ), ALLEXCEPT ( 'Table', 'Table'[Name] ) )

For Median(Metric), I'm not sure what's the correct calculation logic... Could you please provide me the calculation logic of Median(Metric)? Thank you.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards

Re: SUM on 2 columns ignore the third but filter third

sun-sboyanapall — Thu, 21 Apr 2022 18:38:14 GMT

Thank You This worked,

I made an adjustment to the formula since I wanted Date to also affect the Sum & average along with Name.

Sum(Metric) = CALCULATE ( SUM ( 'Table'[Metric] ), ALLEXCEPT ( 'Table', 'Table'[Name],'Table'[Date] ) )

Average(Metric) = CALCULATE ( DIVIDE ( [Sum(Metric)], CALCULATE ( DISTINCTCOUNT ( 'Table'[Date] ) ) ), ALLEXCEPT ( 'Table', 'Table'[Name],'Table'[Date] ) )

Re: SUM on 2 columns ignore the third but filter third

sun-sboyanapall — Thu, 21 Apr 2022 18:42:46 GMT

Sorry, I must have confused you.

So SUM will still be 8 but the average when grouped by Name and Date will be average of (4, 3, 1) Which is 2.66 and Median will be median of (4, 3, 1) which is 3.

Hope that helped!