Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@Gnaglor,
Assuming your configuration is like so
try this measure:
= IF ( HASONEVALUE ( Table2[Date] ); DIVIDE ( COUNTROWS ( Table1 ); MAX ( Table2[OnRent] ) ) )
Thank you, Gravanita! This example helped me get the percentages I was looking for. Now to see if I can get these by month.
@Gnaglor,
Assuming your configuration is like so
try this measure:
= IF ( HASONEVALUE ( Table2[Date] ); DIVIDE ( COUNTROWS ( Table1 ); MAX ( Table2[OnRent] ) ) )
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
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.
This might work:
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |