- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

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

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Need help figuring percentage of two columns in tw...

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

Gnaglor

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-28-2018
01:12 PM

Hello. Hopefully I will explain what I'm trying to do properly. I'll start by showing what some of my data looks like.

My goal is to divide the number of rows with a particular date from the first table by the value matched with the same particular date in the other table.

For example, if I have 40 rows in the first table with a date of 01/01/2017 and one row in the second table with the same date and a value of 7465, I want to have 40/7465 and so on with each individual date.

Is there a way I can accomplish this within Power BI?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Gravanita

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-30-2018
12:40 AM

@Gnaglor,

Assuming your configuration is like so

try this measure:

= IF ( HASONEVALUE ( Table2[Date] ); DIVIDE ( COUNTROWS ( Table1 ); MAX ( Table2[OnRent] ) ) )

6 REPLIES 6

Arslan

Frequent Visitor

Re: Need help figuring percentage of two columns in two tables based on date.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-28-2018
01:39 PM

This might work:

Column = DIVIDE(Table2[On Rent],CALCULATE(COUNTROWS(Table1),RELATEDTABLE(Table1)),0)

Hasan

Gnaglor

Frequent Visitor

Re: Need help figuring percentage of two columns in two tables based on date.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-29-2018
07:06 AM

Thanks for the suggestion Hasan. Unfortunately I could not get this formula to work. The way it is currently written, the numerator and denominator are swapped from the desired result. I tried changed the forumla to,

Measure = DIVIDE(CALCULATE(COUNTROWS('Samanage Incidents'), RELATEDTABLE('Samanage Incidents')), 'Rental Agreement Statistics'[On Rent],0)

but I get the error message "A single value for column 'On Rent' in table 'Rental Agreement Statistics' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

v-yulgu-msft

Community Support Team

Re: Need help figuring percentage of two columns in two tables based on date.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-29-2018
11:42 PM

Hi @Gnaglor,

You can new a calculated column (not a measure) in first table.

Column = CALCULATE ( COUNT ( Table1[MonthDayYear] ), ALLEXCEPT ( Table1, Table1[MonthDayYear] ) ) / LOOKUPVALUE ( Table2[On Rent], Table2[Date], Table1[MonthDayYear] )

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu

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

If this post

Gravanita

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-30-2018
12:40 AM

@Gnaglor,

Assuming your configuration is like so

try this measure:

= IF ( HASONEVALUE ( Table2[Date] ); DIVIDE ( COUNTROWS ( Table1 ); MAX ( Table2[OnRent] ) ) )

Gnaglor

Frequent Visitor

Re: Need help figuring percentage of two columns in two tables based on date.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-30-2018
08:49 AM

Thanks for the response, Yuliana.

When I tried your code, I ended up getting incorrect percentages. When I took the code down to just the calculate formula, I was getting incorrect numbers.

For example, in the calculated column for the date of 09.28.17, I was getting a value of 61 when the number should be 40 as there are only 40 rows in my data with that same date.

I'm not familiar enough with DAX yet to be able to say why that is happening.

Gnaglor

Frequent Visitor

Re: Need help figuring percentage of two columns in two tables based on date.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-03-2018
07:53 AM

Thank you, Gravanita! This example helped me get the percentages I was looking for. Now to see if I can get these by month.