- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- Power BI forums
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Re: SUM on 2 columns ignore the third but filter t...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

SUM on 2 columns ignore the third but filter third

04-18-2022
01:06 PM

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,

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-20-2022
11:33 PM

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

How to upload PBI in Community

Best Regards

Community Support Team _ Rena

If this post**helps**, then please consider *Accept it as the solution** to help the other members find it more quickly.*

If this post

8 REPLIES 8

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-18-2022
10:00 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-19-2022
07:53 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-20-2022
11:33 PM

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

How to upload PBI in Community

Best Regards

Community Support Team _ Rena

If this post**helps**, then please consider *Accept it as the solution** to help the other members find it more quickly.*

If this post

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-21-2022
11:38 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-19-2022
10:00 PM

@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**"

Please provide more details and provide a sample of expected results in the expected visual (table, chart, matrix, card, etc..)?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-21-2022
11:42 AM

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-18-2022
01:30 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-18-2022
01:36 PM

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

Announcements

Check out the September 2024 Power BI update to learn about new features.

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors

User | Count |
---|---|

25 | |

20 | |

18 | |

18 | |

15 |

Top Kudoed Authors

User | Count |
---|---|

36 | |

19 | |

19 | |

17 | |

11 |