cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gnaglor Frequent Visitor
Frequent Visitor

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

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

 

bi1.jpgbi2.jpg

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Gravanita Regular Visitor
Regular Visitor

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

@Gnaglor,

Assuming your configuration is like so

Capture.JPG

 

Capture2.JPG

 


try this measure:

=
IF (
    HASONEVALUE ( Table2[Date] );
    DIVIDE ( COUNTROWS ( Table1 ); MAX ( Table2[OnRent] ) )
)
6 REPLIES 6
Arslan Frequent Visitor
Frequent Visitor

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

This might work:

 

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

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

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.
Community Support Team
Community Support Team

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

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.
Gravanita Regular Visitor
Regular Visitor

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

@Gnaglor,

Assuming your configuration is like so

Capture.JPG

 

Capture2.JPG

 


try this measure:

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

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

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
Frequent Visitor

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

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