SUM on 2 columns ignore the third but filter third

[sun-sboyanapall]

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,

[tamerj1]

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?

[sun-sboyanapall]

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. 

[Anonymous · Accepted solution]

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

[sun-sboyanapall]

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

[tamerj1]

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

[sun-sboyanapall]

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! 

[Whitewater100]

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

[sun-sboyanapall]

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