Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply

SUM on 2 columns ignore the third but filter third

Hello, 

 

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

 

 

I have a table with 4 columns

NameDateTypeMetric
A12/1/2020 A1
A12/1/2020 B5
A12/1/2020 C6
A12/1/2020 

1

B12/1/2020 A

1

B12/1/2020 B

3

B12/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,

 

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

yingyinr_0-1650522663734.png

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

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.

View solution in original post

8 REPLIES 8
tamerj1
Super User
Super User

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?

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: 

 

NameDateTypeMetric
A11/1/2021A1
A11/1/2021A1
A11/1/2021A2
A11/5/2021B3
A12/6/2021B1

 

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

 

Hope that helps. 

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

yingyinr_0-1650522663734.png

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

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.

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

sunsboyanapall_0-1650566262133.png

 

 

 

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

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
Solution Sage
Solution Sage

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

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.