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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Gnaglor
Regular 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
Anonymous
Not applicable

@Gnaglor,

Assuming your configuration is like so

Capture.JPG

 

Capture2.JPG

 


try this measure:

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

View solution in original post

6 REPLIES 6
Gnaglor
Regular Visitor

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

Anonymous
Not applicable

@Gnaglor,

Assuming your configuration is like so

Capture.JPG

 

Capture2.JPG

 


try this measure:

=
IF (
    HASONEVALUE ( Table2[Date] );
    DIVIDE ( COUNTROWS ( Table1 ); MAX ( Table2[OnRent] ) )
)
v-yulgu-msft
Employee
Employee

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.

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.

Anonymous
Not applicable

This might work:

 

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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