- 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

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
- Desktop
- Calculation between columns on a table

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

Calculation between columns on a table

09-29-2016
08:53 AM

Good day.

i have a Table with 1.700.000 rows for the columns "econres", "categoria", "consume".

Categoria column has 5 values (R,I,P,C,M).

I need to do: sum "econres" if "categoria" = "R" and sum "consume" if "categoria" = "R", then divide the resulted sum of "consume" and "econres", because i need the value (consume per econres on the categoria "R")

Thks

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

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

09-29-2016
09:31 AM

Two ways to do this. First, if you only need this for categoria "R" then you can explicitly write the measure to only use that:

consume per econres categoria R = CALCULATE( DIVIDE( SUM(TableName[consume]), SUM(TableName[econres]) ), FILTER( TableName, TableName[categoria] = "R" ) )

The second method would apply if you also want to do the same calculation for categorias I, P, C, and M. In that case you can simply write the basic form of the above measure:

consume per econres = DIVIDE( SUM(TableName[consume]), SUM(TableName[econres]) )

...and plot that measure against the categoria column (that is, use categoria as the rows in a matrix, or the legend or X axis in some chart). The context from that column will make the measure give you each categoria, behaving just like the filter statement in my first example.

Proud to be a Super User!

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

09-29-2016
12:49 PM

@bolabuga you can have more than one filter and more than one condition per filter.

If you want to filter based on more than one column in the same table:

consume per econres categoria R = CALCULATE( DIVIDE( SUM(TableName[consume]), SUM(TableName[econres]) ), FILTER( TableName, TableName[categoria] = "R" && TableName[OtherColumn] = "X" ) )

&& is "and", meaning both conditions must be met. Substitute that with || if you want to do an "or" condition. You can string together as many conditions as you like this way.

If you have two tables connected by a relationship, and you want to filter based on conditions in both tables:

consume per econres categoria R = CALCULATE( DIVIDE( SUM(TableName[consume]), SUM(TableName[econres]) ), FILTER( TableName, TableName[categoria] = "R" && TableName[OtherColumn] = "X" ), FILTER( OtherTable, OtherTable[ColumnName] = "Y" ) )

Proud to be a Super User!

6 REPLIES 6

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

09-29-2016
09:31 AM

Two ways to do this. First, if you only need this for categoria "R" then you can explicitly write the measure to only use that:

consume per econres categoria R = CALCULATE( DIVIDE( SUM(TableName[consume]), SUM(TableName[econres]) ), FILTER( TableName, TableName[categoria] = "R" ) )

The second method would apply if you also want to do the same calculation for categorias I, P, C, and M. In that case you can simply write the basic form of the above measure:

consume per econres = DIVIDE( SUM(TableName[consume]), SUM(TableName[econres]) )

...and plot that measure against the categoria column (that is, use categoria as the rows in a matrix, or the legend or X axis in some chart). The context from that column will make the measure give you each categoria, behaving just like the filter statement in my first example.

Proud to be a Super User!

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

05-04-2022
12:54 PM

Thanks! This is exactly what I needed!

My final based on your sample is here:

Measure2 = CALCULATE(DIVIDE(SUMX(Table1,[measure1]*[Column1]),[measure1]), FILTER(Table1, Table1[Column1] <> 999))

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

09-29-2016
12:43 PM

would it be possible to add more than 1 filter in the "calculate" command??

consume per econres categoria R = CALCULATE( DIVIDE( SUM(TableName[consume]), SUM(TableName[econres]) ), FILTER( TableName, TableName[categoria] = "R" ) )

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

09-29-2016
12:49 PM

@bolabuga you can have more than one filter and more than one condition per filter.

If you want to filter based on more than one column in the same table:

consume per econres categoria R = CALCULATE( DIVIDE( SUM(TableName[consume]), SUM(TableName[econres]) ), FILTER( TableName, TableName[categoria] = "R" && TableName[OtherColumn] = "X" ) )

&& is "and", meaning both conditions must be met. Substitute that with || if you want to do an "or" condition. You can string together as many conditions as you like this way.

If you have two tables connected by a relationship, and you want to filter based on conditions in both tables:

consume per econres categoria R = CALCULATE( DIVIDE( SUM(TableName[consume]), SUM(TableName[econres]) ), FILTER( TableName, TableName[categoria] = "R" && TableName[OtherColumn] = "X" ), FILTER( OtherTable, OtherTable[ColumnName] = "Y" ) )

Proud to be a Super User!

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

09-29-2016
12:53 PM

Nice Explanation, really thks KHorseman.

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

09-29-2016
12:37 PM

Thks KHorseman, took me sometime to unsderstand i have the value i want repeated all across the 1.7kk rows and i can just throw it on my graphic and choose to show "average" instead of "sum".

obs: i cant say for sure the option correct translation is average, for me its showing in portuguese "media"

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.

Featured Topics

Top Solution Authors

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

104 | |

98 | |

97 | |

40 | |

38 |

Top Kudoed Authors

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

151 | |

122 | |

78 | |

73 | |

67 |